How to Use the LOOKUP Function in Excel

ByRick

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.

LOOKUP (Vector Form) Diagram

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])

LOOKUP (Vector form) Examples

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.

LOOKUP Array form Diagram

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.

Similar Posts

Using SUMIF, COUNTIF and Similar “IF” Functions

Using SUMIF, COUNTIF and Similar “IF” Functions

ByRick

Do you need totals, an average, or a count of cells in a range, but only under certain circumstances?  Then one of the several statistical “IF” functions will be useful.   They let you use a criterion, or several criteria, to include or ignore chosen cells. They are:COUNTIF, COUNTIFS count those cells in a range that…

|

Flowchart: Which Excel LOOKUP Function Should I Use?

ByRick

When you want to look up data in Excel, you can choose from various functions: LOOKUP (in two forms), VLOOKUP, HLOOKUP, and the combination of INDEX(MATCH). Which should you use?  Here’s a flowchart to help guide you: The first and most important question is, “do you want an exact match or an approximate match?” If…

Use INDEX(MATCH) instead of an Excel LOOKUP Function

ByRick

For some lookups, the INDEX and MATCH functions used together may be the solution.  Together, they are more robust and versatile than VLOOKUP, HLOOKUP, and LOOKUP. MATCH returns the position of the matched item in a one-dimensional list. It can do an exact or approximate match.  MATCH(lookup_value, lookup_array, 0) does an exact match. INDEX returns…

| |

How to Use a Worksheet Function in a Macro: Proper Case Names

ByRick

In some cases, VBA does not have a specific function, but the Excel Worksheet does. This Excel Visual Basic (VBA) code example demonstrates: How to call that Worksheet Function from a Macro. In this particular example, how to call the Worksheet Function =PROPER() from VBA. The function to downcase parts of surnames that normally appear…