Excel LOOKUP Function (Table of Contents)
LOOKUP In Excel
LOOKUP function is an inbuilt function categorized under lookup and reference functions.
When you need to search for specific data in one row or one column, it will use the lookup function; its main purpose is to Lookup a value in a one-column or row range or an array (two-dimensional array (table) of data.
LOOKUP Function is not case-sensitive.
LOOKUP Formula in Excel
There are 2 types of formulas for the LOOKUP function.
1. Formula of the vector form of Lookup
LOOKUP (lookup_value, lookup_vector, [result_vector])
2. Formula of the Array form of Lookup
LOOKUP (lookup_value, array)
Arguments of LOOKUP formula in Excel
LOOKUP Formula has the following arguments:
- Lookup_value: A value that LOOKUP searches for in an array, “Array” is a collection of values in rows and columns; it can be a text, number or a reference to a cell containing the lookup value
- Lookup_vector: vector refers to a one-column or one-row range. It’s a single column or single row of data that is sorted in ascending order, where the LOOKUP function searches for a value in this range or when you want to specify the range or table array that contains the values that you want to match.
Note: Prior to performing a lookup function, it must be sorted in ascending order.
- Result_vector: It is a single row or single column of data that is of the same size. one-row or one-column range from where you want to return the result, or It is the column or a row from where you need to fetch the required value
- Array: is the lookup table where you want to search for the lookup value. Mostly it will have a two-dimensional array (table) of data, values in the column or row of the array,
Note: Both the text & numeric data in the array must be sorted in ascending order.
The array form of Lookup
Note: Vlookup & Hlookup is used instead of an array form of lookup because it has a limited option.
The difference is that VLOOKUP searches in the first column, HLOOKUP searches for lookup_value in the first row and LOOKUP searches according to an array’s dimensions.
How to Use the LOOKUP Function in Excel?
LOOKUP Function is straightforward, easy to use. Let us now see how to use the LOOKUP function in Excel with the help of some examples.
Example #1 – Vertical Lookup formula
If your source data appears to be a vertical layout, i.e. the entries reside in columns rather than rows, it searches in a one-column range.
Here, the table array contains the student name & registration id, which is arranged vertically in columns.
For a below-specified student name, require a registration id detail?
To obtain a registration_id, will apply lookup formula,
Under the formula toolbar, click on lookup & reference,
In that select LOOKUP function, a Pop-up will need to fill the function arguments to obtain the desired result.
- Lookup_value: is the value to search for. Here we need to look up “Smith” or B6 in a specified column range.
- Lookup_vector: it is the range that contains one column of text; here it is “E6:E10”, which is arranged vertically
- Result_vector: one-column range from which you want to return the result; here it is “F6:F10”, which is arranged vertically.
Example #2 – Horizontal Lookup formula
If your source data appears to be a horizontal layout, i.e. the entries reside in rows rather than columns.
Here, the table array contains the student name & registration id, which is arranged horizontally in rows.
Similar to the first example, here in lookup_vector & result vector, we need to select the row range for both to obtain the desired result
- Lookup_value: is the value to search for. Here we need to look up “Smith” or B6 in a specified row range.
- Lookup_vector: it is the range that contains one row of text; here it is “G6:K6”, which is arranged horizontally
- Result_vector: one-row range from which you want to return the result; here it is “G7:K7”, which is arranged horizontally.
Things to consider prior to the application of vector form of LOOKUP
- The Values in lookup_vector should be sorted in ascending order, i.e. from largest to smallest in case of numeric data or from A to Z in case of text data; otherwise, your Excel Lookup formula may return an error or incorrect result.
- Result_vector & Lookup_vector must be a one-row or one-column range of the same size.
- The LOOKUP function is not case-sensitive; it does not differentiate lowercase and uppercase text. Uppercase and lowercase characters are considered equivalent.
- The Lookup formula searches for an exact match. If lookup_value doesn’t find the relevant value in lookup_vector, the LOOKUP Function returns the #N/A error.
Below mentioned examples illustrate an array form of LOOKUP Formula
Array Lookup has 2 arguments
Formula: LOOKUP (lookup_value, array)
Lookup_value – a value to search for in an array
an array is the lookup table where you want to search for the lookup value. values in the first column or row of the array,
Note: Both the text & numeric data in the array must be sorted in ascending order.
For example: In the array, the name is located in the first column of the array (column E) and Rating in the last column of the array (column F); you can use the following formula to search the name and pull out the rating info
Example #3 – LOOKUP (lookup_value, array)
B12:C23 is lookup-range, and E12 is the lookup_value. The formula result will be May as a result of the lookup value (5).
Note:
- If the array has more columns than rows, LOOKUP searches in the first row (vertical lookup).
- If the array has more rows than columns or the same number of columns and rows, a Lookup formula searches in the first column (horizontal lookup).
- If a formula can’t find the lookup value, it uses the largest value in the array that is less than or equal to lookup_value.
Recommended Articles
This has been a guide to LOOKUP. Here we discuss the LOOKUP Formula and how to use the LOOKUP function in Excel and practical examples and downloadable excel templates. You can also go through our other suggested articles –
