Multi-line VLOOKUP

I have a data base that has five separate rows for each item. I can use VLOOKUP to find the first row’s details, but how do I access the details in the other four lines?

By Neale Blackwood

This is an ideal situation to use the INDEX / MATCH function combination. These two functions work well together and allow you to do flexible lookups.

Assuming the data base is in a sheet called Data and the codes are in column A. In another sheet the code to find is in cell A2. In the same sheet the following formula will find the entry in column B of the first row in the Data sheet matching the code – similar to the VLOOKUP.

Cell B2

=INDEX(Data!B:B,MATCH($A$2,Data!$A:$A,0)+ROW()-ROW($B$2),1)

This formula can be copied down to cells B3, B4, B5 and B6 to get the remaining four rows of data. This formula can also be copied across to get the details from the other columns in the Data sheet.

Cell C2

=INDEX(Data!C:C,MATCH($A$2,Data!$A:$A,0)+ROW()-ROW($B$2),1)

The INDEX function allows you to refer to a cell via its row and column numbers within a range. If you refer to a whole column, as above, this becomes the actual row number. The 1 at the end of the formula is required because the INDEX is using a single column.

The MATCH function returns a number that represents the relative position of an item in a range. Since we search the whole column in the MATCH function it will return the row number of the first row that matches the code in the Data sheet.

The +ROW()-ROW($B$2) part of the formula increments the MATCH row number by one as the formula is copied down the sheet. The ROW() function returns the current row number.

Note: The formula in B2 refers to itself in ROW($B$2). This doesn’t cause a circular reference because we are not using the result of B2, we are only getting its row number. ROW($B$2) will always return the number 2.

You can refer to the Excel help system to get more details on the functions above.