Lookup Functions

Lookup functions search for values in a table.
Each lookup function uses a different method for searching and returning a value.


Worksheet Functions

LOOKUPReturns the value in a row or column that matches a value in a row or column.
HLOOKUPThis function searches for a value in a particular row and returns an item from the same column.
Returns the value in a row that matches a value in the top row of a table.
The lookup column has to be the first column.
It can only return columns to the right.
It can only find the first occurrence.
VLOOKUPThis function searches for a value in a particular column and returns an item from the same row.
Returns the value in a column that matches a value in the first column of a table.
The lookup column has to be the first column.
It can only return columns to the right.
It can only find the first occurrence.
MATCHReturns the position of a value in an array or list.
INDEXReturns the value from a cell range which is the intersection of a row and a column.


Performing a Two Way Lookup

This assumes you want to cross reference by using a value in the first column and a value in the first row.
There are a number of ways to do this:

1) VLOOKUP and MATCH

2) HLOOKUP and MATCH

3) OFFSET and MATCH

4) INDEX and MATCH

Lets consider the following table where we want ???


 


VLOOKUP and MATCH

The VLOOKUP function can be used to find the value in a column that matches a value in the first column of a table.
The MATCH function can be used to find the position of a value in a particular cell range.
The following formula will return the cross reference of a particular column heading with a particular row heading.

=VLOOKUP("col_item",B2:L12,MATCH("row_item",B2:L2,0))

"col_item" is the heading that you want to find in the list of column headings (C2:L2).
"row_item" is the heading that you want to find in the list of row headings (B3:B12).


The following formula will return the cross reference of Bristal across and Aberdeen down.

=VLOOKUP("Bristol",B2:L12,MATCH("Aberdeen",B2:L2,0)) = 513

The following formula will return the cross reference of London across and Leeds down.

=VLOOKUP("London",B2:L12,MATCH("Leeds",B2:L2,0)) = 198


VLOOKUP and MATCH - Two way lookup in a table with duplicates

Lets assume that you are using the VLOOKUP and MATCH combination to perform your cross reference.
If you have any duplicates in your column headings the first matching column will be used.
If you have any duplicates in your row headings the last matching row will be used.


HLOOKUP and MATCH

The HLOOKUP function can be used to find the value in a row that matches a value in the first row of a table.
The MATCH function can be used to find the position of a value in a particular cell range.
The following formula will return the cross reference of a particular column heading with a particular row heading.

=HLOOKUP("row_item",B2:L12,MATCH("col_item",B2:B12,0))

"col_item" is the heading that you want to find in the list of column headings (C2:L2).
"row_item" is the heading that you want to find in the list of row headings (B3:B12).
Notice that the row identifier is the first argument used.


The following formula will return the cross reference of Aberdeen across and Bristol down.

=HLOOKUP("Aberdeen",B2:L12,MATCH("Bristol",B2:B12,0)) = 513

The following formula will return the cross reference of Leeds across and London down.

=HLOOKUP("Leeds",B2:L12,MATCH("London",B2:B12,0)) = 198


Looking up Values in any Column

The VLOOKUP and HLOOKUP functions can be used to lookup values in a table although there are a few restrictions about how they can be used.
For example the VLOOKUP function cannot be used to lookup values in any column, but can only lookup values in the first column.
What if the data you want to return is to the left of the criteria column?
There are a number of ways to do this:

5) OFFSET and MATCH

6) INDEX and MATCH

Lets consider the following table where we want to lookup a "Last Name" and return a "First Name".
In this example re-ordering the columns is not an option and therefore the VLOOKUP function cannot be used.


 


OFFSET and MATCH

This type of lookup is also known as a cross-reference or two dimensional lookup.
You can lookup values in any column (or row) by combining the MATCH and OFFSET functions.
The OFFSET function can be used to locate the exact cell within the table by offsetting from the top left corner.
The MATCH function can be used to find the position of a value in a particular cell range.
The horizontal list "C2:L2" and the vertical list "B3:B12" do not have to be sorted into alphabetical order.
Below are two examples: one with sorted data and one without.


OFFSET and MATCH - Sorted Data

The following formula will return the cross reference of a particular column heading with a particular row heading.

=OFFSET("top_left_cell",MATCH("row_item",C2:L2,0),MATCH("col_item",B3:B12,0))

"top_left_cell" is the cell in the top left corner of the table.
"row_item" is the heading that you want to find in the list of row headings (B3:B12).
"col_item" is the heading that you want to find in the list of column headings (C2:L2).
Notice that the row identifier is specified in the first MATCH function (i.e. down then along, rather than along then down).


The following formula will return the cross reference of Bristal (across) and Aberdeen (down).

=OFFSET($B$2,MATCH("Aberdeen",C2:L2,0),MATCH("Bristol",B3:B12,0)) = 513


The following formula will return the cross reference of London (across) and Leeds (down).

=OFFSET($B$2,MATCH("Leeds",C2:L2,0),MATCH("London",B3:B12,0)) = 198


INDEX and MATCH

=INDEX( MATCH( ) )
=INDEX( MATCH( ), MATCH( ) )
This is the formula that the Lookup Wizard add-in will automatically create for you.
The first MATCH returns the position of the first item along the top.
The second MATCH returns the position of the second item down the side.
The INDEX function is then used to offset the correct cell from the top left corner.


To make this type of two way lookup simpler to maintain you might want to create a named range for the whole table and then use a formula that refers to that named range.
Lets assume you have the following table and the highlighted cells have the named range "NamedRange".
SS - above with cells highlighted
=INDEX( MATCH( INDEX() ), MATCH( INDEX() ) )
={INDEX(NamedRange, MATCH("Leeds", INDEX(NamedRange,,1),0),MATCH("Bristol",INDEX(NamedRange,1),))}

Lookup Wizard

The Lookup Wizard was an additional add-in (removed in Excel 2010) that can be used to cross reference a particular column and row from a table.
This wizard will create a formula that is a combination of the INDEX and MATCH functions.
For more information regarding this add-in, please refer to the Lookup Wizard page.


 

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext