Many of us are familiar with the VLOOKUP function. For those who are not, VLOOKUP is an Excel function to help you find values in a table or range. You will need to indicate the column number which you want the value to return. For example:

=VLOOKUP("12345", A10:C20, 2, TRUE)

This returns the value in the second column. The value 2 is keyed in manually. Is there a way to further automate it?

A practical example

Say I am preparing a store performance report where I need to refer to some raw data downloaded from my accounting system. The raw data sits in a worksheet called RawData. To organise the sales data for each store into a summary table, I write the following formula:

=VLOOKUP(A8, RawData!$A$5:$D$12, 2, FALSE)

In plain English, this formula first locates the row for Store A (the value in cell A8) within the range A5:D12 of the RawData worksheet, and returns the data in the second column of that row — which is the sales figure.

If I want to pull the COGS data for Store A instead, I copy and paste the formula and change the 2 to 3, because the COGS data is in the third column. That works, but it’s manual — and fragile if someone inserts a column.

Using MATCH to automate the column number

The MATCH function returns the relative position of an item in a range of cells. For example, inside the RawData worksheet:

=MATCH("Sales", A4:D4, 0)

This returns 2 because “Sales” is the second item in the cell range A4:D4.

You may have guessed what comes next — we can nest MATCH inside VLOOKUP to replace the hardcoded column number:

=VLOOKUP($A8, RawData!$A$5:$D$12, MATCH($B$5, RawData!$A$4:$D$4, 0), FALSE)

Now the column number is derived automatically from the header row. Change the column header reference in B5 and the formula follows — no manual editing required.

P.S. One further question for you — in the formula above, why did I choose to use dollar signs differently for $A8 versus $B$5?