Return to Excel Formulas List

VLOOKUP & MATCH Combined – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to retrieve data from multiple columns using the MATCH and VLOOKUP Functions in Excel and Google Sheets.

vlookup match combined

 

Why Should you Combine VLOOKUP and MATCH?

Traditionally, when using the VLOOKUP Function, you enter a column index number to determine which column to retrieve data from.

This presents two problems:

  • If you want to pull values from multiple columns, you must manually enter the column index number for each column
  • If you insert or remove columns, your column index number will no longer be valid.

To make your VLOOKUP Function dynamic, you can find the column index number with the MATCH Function.

=VLOOKUP(G3,B3:E5,MATCH(H2,B2:E2,0),FALSE)

vlookup match combined 01

Let’s see how this formula works.

MATCH Function

The MATCH Function will return the column index number of your desired column header.

In the example below, the column index number for “Age” is calculated by the MATCH Function:

=MATCH("Age",B2:E2,0)

vlookup match combined 02

“Age” is the 2nd column header, so 2 is returned.

Note: The last argument of the MATCH Function must be set to 0 to perform an exact match.

VLOOKUP Function

Now, you can simply plug in the result of the MATCH Function into your VLOOKUP Function:

=VLOOKUP(G3,B3:E5,H3,FALSE)

vlookup match combined 03

Replacing the column index argument with the MATCH Function gives us our original formula:

=VLOOKUP(G3,B3:E5,MATCH(H2,B2:E2,0),FALSE)

vlookup match combined 04

Inserting and Deleting Columns

Now, when you insert or delete columns in the data range, the result of your formula will not change.

vlookup match combined 05

In the example above, we added the Teacher column to the range but still want the student’s Age. The output from the MATCH Function identifies that “Age” is now the 3rd item in the header range, and the VLOOKUP Function uses 3 as the column index.

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

=VLOOKUP(G3,B3:E5,MATCH(H2,B2:E2,0),FALSE)

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

=VLOOKUP($G3,$B$3:$E$5,MATCH(H$2,$B$2:$E$2,0),FALSE)

Read our article on Locking Cell References to learn more.

VLOOKUP & MATCH Combined in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

vlookup match combined Google