excelexcel-formulaexcel-match

Difference between Vlookup() and Match()

I want to perform a search on column 2 to find keywords that are missing in Column 1. I used the formula =IF(MATCH(G2,$E$2:$E$117,0),1,"No") so for rows that return 1, it means that the keyword exists in column 1. When a keyword in Column 2 cannot be found in Column 1, my formula will return #N/A. (Image attached)

However, I would like to understand whether this can be done in a much simpler way, perhaps using vlookup function? If so, how?

I am also unsure about the difference between this formula and the vlookup.

Hope someone experienced with Excel functions could explain it to me. Thank you in advance.

enter image description here

Cheers.

Solution

  • MATCH returns the relative location of the value in the range, while VLOOKUP will return the value.

    When using MATCH wrap it in ISNUMBER to deal with the fact that if not found the MATCH will return an Error:

    =IF(ISNUMBER(MATCH(G2,$E$2:$E$117,0)),1,"No")

    Now instead of an error you will get No in the field when not found.

    Another method would be to use COUNTIF:

    =IF(COUNTIF($E$2:$E$117,G2),1,"No")

    Although shorter, it will be slower than the MATCH version. It may not be noticeable with the limited data set but too many(10,000+) will cause problems.

    To use VLOOKUP:

    =IF(ISERROR(VLOOKUP(G2,$E$2:$E$117,1,FALSE)),"No",1)