Difference between Vlookup() and Match()

Question

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.

Cheers.

Answer 1

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)

Answer 2

This would be the perfect occasion to try the Brand NEW

XLOOKUP (wooo)

XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number.

Syntax

=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

Arguments

lookup - The lookup value.

lookup_array - The array or range to search.

return_array - The array or range to return.

not_found - [optional] Value to return if no match found.

match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.

search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.