How to Use the LOOKUP Function in Excel
The LOOKUP Function comes in two forms, the Vector form and the Array form. The former is by far the most useful and distinctive. Both forms search for an approximate match in an ordered range, and then return a value. The Vector form uses one-dimensional ranges (one or two of them). The Array form uses a single range of one or two dimensions.
LOOKUP (Vector form) Description
The Vector form of the LOOKUP function in Microsoft Excel searches for a value in a sorted vertical or horizontal range. Having found the nearest match, it returns the value in the same position from a second vertical or horizontal range. LOOKUP always finds the nearest match: It cannot be set to find only an exact match.
Use LOOKUP (Vector form) if:
- the column of comparison values is not left of the column of data to return (VLOOKUP), or
- the row of comparison values is not above the row of data to return (HLOOKUP), or
- the comparison values and the data to return are not in the same range, or the same vertical or horizontal format, or even the same worksheet.
Use it particularly if you want the nearest match, not an exact match.
LOOKUP (Vector form) Syntax and Examples
LOOKUP(lookup_value, lookup_vector, [result_vector])
In the examples above:
In cell A16 we have “Gizmo”. We use it as the lookup_value in cell B16. That formula =LOOKUP(A16, A4:A13, D4:D13) gives the result 1463. LOOKUP finds an exact match for the lookup_value “Gizmo” in the 5th cell of the lookup_vector range. Hence it returns the 5th value from the result_vector.
In cell A17 we have “Thing”. In cell B17 the formula =LOOKUP(A17, A4:A13, D4:D13) gives the result 1463, corresponding to “Gizmo”. Why? The lookup_value “Thing” does not occur in the lookup_vector. Alphabetically, it comes right before “Thingamabob”, the 6th item in the range. So the function returns the 5th value from the result_vector.
In cell A18 we have “Thing”. In cell B18 the formula =LOOKUP(A18, A4:A13) has no result_vector, so it will return data from the lookup_vector. Again, “Thing” does not occur in the lookup_vector, and comes right before “Thingamabob”, the 6th item in the range. So the function returns the 5th value from the lookup_vector, “Gizmo”.
In cell A19 the formula =IF(A18=B18, “OK”, “Mismatch!”) demonstrates how to check whether the LOOKUP above it found an exact match. It checks the lookup_value against the value returned from the lookup_vector. In this case, it gives the result “Mismatch!”
LOOKUP (Vector form) Discussion
Advantages
- The vector form is immune to insertions between lookup_vector and result_vector.
- Lookup_vector and result_vector can be a mix of horizontal and vertical vectors.
- The same function does both horizontal and vertical lookups.
- The vector form can have a lookup_vector to the right of (or remote from) result_vector.
Disadvantages
- Items searched must be in ascending order
- LOOKUP does an approximate match, there is no way to force an exact match: If you want an exact match you have to write a formula to check if you got one.
Alternatives:
Use the INDEX function with one or two MATCH functions under the same circumstances as LOOKUP (Vector form), but you want an exact match. For exact matches, the data need not be sorted.
Use VLOOKUP when your comparison values are in a column to the left of the data you want to find. Use it particularly if you want an exact match.
Use HLOOKUP when your comparison values are in a row across the top of a table of data, and you want to look down a specified number of rows. Use it particularly if you want an exact match.
Now for the Array form:
LOOKUP (Array form) Description
The Array form of the LOOKUP function in Microsoft Excel searches for a value in the first row or column of a range that is sorted by that row or column. Having found the nearest match, it then returns the value in the same position from the last row or column of that range. LOOKUP always finds the nearest match: It cannot be set to find only an exact match.
The LOOKUP Array form is very similar to HLOOKUP and VLOOKUP. It does a lookup in the first column or row of a sorted range (whichever is longer). It then returns the corresponding value in the last row or column of that same range.
Use it particularly if you want an approximate match, not an exact match.
LOOKUP Array form Syntax and Examples
LOOKUP(lookup_value, array)
Using the above spreadsheet:
=LOOKUP(“Gizmo”, A4:E13) gives the result 1463 from column E on row 8, the row where “Gizmo” occurs in the first column.
=LOOKUP(“Gizmo”, A4:A13) gives the result “Gizmo”, an exact match in column A, the only column.
=LOOKUP(“Thing”, A4:A13) gives the result “Gizmo”. This is an approximate match in column A. “Thing” comes before “Thingamabob”, so the formula returns the value above: “Gizmo”.
=LOOKUP(“Gizmo”, A7:E10) gives the result “Thingamajig”. The range is wider than it is tall, but has text in its first column and numbers elsewhere. This seems to confuse Excel, and it gives an answer that has no obvious logic at all! Do check your results!
LOOKUP (Array form) Discussion
Advantages
- The array form is immune to insertions between the first column or row and the last.
- The same function does both horizontal and vertical lookups.
- Unlike HLOOKUP and VLOOKUP, the Array form is not compromised if rows or columns are inserted within the array. That is because it always uses the last column or row, not a specified one.
Disadvantages
- Items searched must be in ascending order.
- An inexact match is done. There is no way to force an exact match.
- With the array form, there may be confusion about whether it is searching vertically or horizontally.
Alternatives:
Use VLOOKUP when your comparison values are in a column to the left of the data you want to find. Use it particularly if you want an exact match.
Use HLOOKUP when your comparison values are in a row across the top of a table of data, and you want to look down a specified number of rows. Use it particularly if you want an exact match.
Notes
LOOKUP should not be confused with “OOKUP”. That is a literary function used by the Librarian at Unseen University in Terry Pratchett’s humorous fantasy Discworld series.
More here from Microsoft.