Two way lookups

Image this scenario, you have a work sheet of data with months as column headings and account numbers as rows.

You want to provide the current month’s data on a summary sheet.

You can use a VLOOKUP formula to return the current months data, but the following month you need to update the formula.

=VLOOKUP($A5,Data!$A$1:$O$5,6,FALSE)

Each month the column index number would need to be incremented by one in each formula.

=VLOOKUP($A5,Data!$A$1:$O$5,7,FALSE)

Cell Reference

Now one trick is to enter a month number in a cell and reference it from the VLOOKUP formula.

In the example, we would enter a month number into cell B3. The formula would now look like this:

=VLOOKUP($A5,Data!$A$1:$O$5,$B$2+2,FALSE)

Every month a new month number would be entered into cell B3 and the summary page would update.

Match formula

Instead of typing in a month number every month we can lookup the month name in our data sheet headings and use the returned number as the column index number in the VLOOKUP formula.

To do this we use a MATCH Formula.

MATCH($A$2,Data!$A$1:$N$1,0)

This formula would find the month name in the column headings and return it’s position.

This formula can be then embedded in the VLOOKUP formula, in the column index section, it would look like this:

=VLOOKUP($A5,Data!$A$1:$O$5,MATCH($A$2,Data!$A$1:$N$1,0),FALSE)

Data Validation

A potential issue with the previous step is if the user mistypes the month name or adds extra text like a trailing space. This will cause the MATCH to return no value and the VLOOKUP to fail.

To prevent this we can provide a dropdown list for the user to select from.

We can do this by using Data validation.

Click on the Data tab and select Data validation and then Data validation again.

Choose List, tick In-cell dropdown and then in the Source box enter the month names separated by commas.

A dropdown is now available in cell A2 and choosing the month automatically updates the data returned.