How to use the Excel LOOKUP function

The LOOKUP Function is categorized under Excel Lookup and Reference functions. The function performs a rough match lookup either in a one-row or one-column range and returns the corresponding value from another one-row or one-column range.

1. Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])

The LOOKUP function syntax has the following arguments:

  • Lookup_value(required) – a value to search for. It can be a number, text, logical value of TRUE or FALSE, or a reference to a cell containing the lookup value.
  • Lookup_vector(required) – one-row or one-column range to be searched. It must be sorted in ascending order.
  • Result_vector(optional) – one-row or one-column range from which you want to return the result – a value in the same position as the lookup value. Result_vector must be the same size as lookup_range. If omitted, the result is returned from lookup_vector.

Example: Let’s look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

Excel LOOKUP function - How to use the Excel LOOKUP function

Syntax:  =LOOKUP(2226,A2:A8,B2:B8)

Result:

Excel LOOKUP - How to use the Excel LOOKUP function

Based on the Excel spreadsheet above, the following LOOKUP examples would return:

Syntax: =LOOKUP(2226,A2:A8)
Result: 2226

Syntax: =LOOKUP(2225,A2:A8,B2:B8)
Result: Oppo

Syntax: =LOOKUP(2355,A2:A8,B2:B8)
Result: Sophuc.com

Syntax: =LOOKUP(2225,A2:A8)
Result: 2158

Syntax: =LOOKUP(2227,A2:A8)
Result: 2226

Syntax: =LOOKUP(2135,A2:A8,B2:B8)
Result: #N/A

Note: This page describes the vector form of the LOOKUP function. In this case, a vector refers to a one-column or one-row range.

Use the LOOKUP function to look up a value in a one-column or one-row range, and retrieve a value from the same position in another one-column or one-row range. The lookup function has two forms, vector and array. This article describes the vector form.

LOOKUP has default behaviors that make it useful when solving certain problems (i.e. retrieve approximate matched value instead of position, find the value in the last non-empty cell in a row or column, etc.). LOOKUP assumes that values in lookup_vector are sorted in ascending order and always performs an approximate match. When LOOKUP can’t find a match, it will match the next smallest value.

  • LOOKUP assumes that lookup_vector is sorted in ascending order.
  • When lookup-value can’t be found, LOOKUP will match the next smallest value.
  • When lookup_value is greater than all values in lookup_vector, LOOKUP matches the last value.
  • When lookup_value is less than all (i.e. the first) value in lookup_vector, LOOKUP returns #N/A.
  • result_vector must be the same size as lookup_vector.
  • LOOKUP is not case-sensitive

1.2 Syntax: =LOOKUP(lookup_value, array)

The LOOKUP function syntax has the following arguments:

  • lookup_value    Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
    • If LOOKUP can’t find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
    • If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
  • array    Required. A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.
    • If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.
    • If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.
    • With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.

Example: Let’s look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel:

Excel LOOKUP function - How to use the Excel LOOKUP function

1.2.1 Syntax:  =LOOKUP(2226,A2:B8)

Result:

Excel LOOKUP function 1 - How to use the Excel LOOKUP function
Based on the Excel spreadsheet above, the following LOOKUP examples would return:

Syntax: =LOOKUP(2222,A2:B8)
Result: Oppo 

Syntax: =LOOKUP(2355,A2:B8)
Result: Sophuc.com

Syntax: =LOOKUP(2135,A2:B8)
Result: #N/A 

Syntax: =LOOKUP(2490,A2:B8)
Result: Lennovo

Syntax: =LOOKUP(2500,A2:B8)
Result: Lennovo

Syntax: =LOOKUP(9999,A2:B8)
Result: Lennovo

1.2.2 Syntax:  =LOOKUP(2226,A2:C8)

Result:

Excel LOOKUP 1 - How to use the Excel LOOKUP function
Based on the Excel spreadsheet above, the following LOOKUP examples would return:

Syntax: =LOOKUP(2222,A2:C8)
Result: 12.2

Syntax: =LOOKUP(2355,A2:C8)
Result: 16.5

Syntax: =LOOKUP(2135,A2:C8)
Result: #N/A

Syntax: =LOOKUP(2490,A2:C8)
Result: 22.19

Syntax: =LOOKUP(2500,A2:C8)
Result: 22.19

Syntax: =LOOKUP(9999,A2:C8)
Result: 22.19

1.2.3 Syntax:  =LOOKUP(2226,A2:D8)

Result:

LOOKUP function - How to use the Excel LOOKUP function

Based on the Excel spreadsheet above, the following LOOKUP examples would return:

Syntax: =LOOKUP(2222,A2:D8)
Result: 6

Syntax: =LOOKUP(2355,A2:D8)
Result: 9

Syntax: =LOOKUP(2135,A2:D8)
Result: #N/A

Syntax: =LOOKUP(2490,A2:D8)
Result: 32

Syntax: =LOOKUP(2500,A2:D8)
Result: 32

Syntax: =LOOKUP(9999,A2:D8)
Result: 32

Above is how to use the detailed Lookup function in Excel. The Lookup function will be divided into 2 types: vector and array. The formula and usage of these 2 types are also not the same.

Add a Comment

Your email address will not be published. Required fields are marked *