• Using INDEX and MATCH

Obviously, the INDEX and MATCH combination is a mixture of the two named functions. You can take a look at our how-tos for the INDEX function and MATCH function for specific details on using them individually.

To use this duo, the syntax for each is INDEX(array, row_number, column_number) and MATCH(value, array, match_type).

When you combine the two, you’ll have a syntax like this: INDEX(return_array, MATCH(lookup_value, lookup_array)) in its most basic form. It’s easiest to look at some examples.

To find a value in cell G2 in the range A2 through A8 and provide the matching result in the range B2 through B8, you would use this formula:

=INDEX(B2:B8,MATCH(G2,A2:A8))

If you prefer to insert the value you want to find instead of using the cell reference, the formula looks like this where 2B is the lookup value:

=INDEX(B2:B8,MATCH("2B",A2:A8))

Our result is Houston for both formulas.

We also have a tutorial that goes into detail on using INDEX and MATCH should that be your choice.

• Using VLOOKUP

VLOOKUP has been a popular reference function in Excel for some time. The V stands for Vertical, so with VLOOKUP, you’re doing a vertical lookup and it’s from left to right.

The syntax is VLOOKUP(lookup_value, lookup_array, column_number, range_lookup) with the last argument optional as True (approximate match) or False (exact match).

Using the same data as that for INDEX and MATCH, we’ll look up the value in cell G2 in the range A2 through D8 and return the value in the second column that matches. You’d use this formula:

=VLOOKUP(G2,A2:D8,2)

As you can see, the result using VLOOKUP is the same as using INDEX and MATCH, Houston. The difference is that VLOOKUP uses a much simpler formula. For more details on VLOOKUP, check out our how-to.

So why would anyone use INDEX and MATCH instead of VLOOKUP? The answer is because VLOOKUP only works when your lookup value is to the left of the return value you want.

If we did the reverse and wanted to look up a value in the fourth column and return the matching value in the second column, we would not receive the result we want and may even receive an error. As Microsoft writes:

Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

INDEX and MATCH covers the entire cell range or array making it a more robust lookup option even if the formula is a bit more complicated.

• Using XLOOKUP

XLOOKUP is a reference function that arrived in Excel after VLOOKUP and the counterpart HLOOKUP (horizontal lookup). The difference between XLOOKUP and VLOOKUP is that XLOOKUP works no matter where the lookup and return values reside in your cell range or array.

The syntax is XLOOKUP(lookup_value, lookup_array, return_array, not_found, match_mode, search_mode). The first three arguments are required and are similar to that in the VLOOKUP function. XLOOKUP offers three optional arguments at the end for giving a text result if the value isn’t found, a mode for the type of match, and a mode for how to perform the search.

For the purpose of this article, we’ll concentrate on the first three required arguments.

Back to our cell range from earlier, we’ll look up the value in G2 in the range A2 through A8 and return the matching value from the range B2 through B8 with this formula:

=XLOOKUP(G2,A2:A8,B2:B8)

And like with INDEX and MATCH as well as VLOOKUP, our formula returned Houston.

We can also use a value in the fourth column as the lookup value and receive the correct result in the second column:

=XLOOKUP(20745,D2:D8,B2:B8)

With this in mind, you can see that XLOOKUP is a better option than VLOOKUP simply because you can arrange your data any way you like and still receive your desired result.