Lookup formula in excel means referencing a cell as a reference to match values against the cell in another row or column and retrieve the matching output from the respective rows and columns, there are two type of lookup formula in excel HLOOKUP or horizontal lookup which is used to reference within rows and VLOOKUP or vertical lookup which is used to reference within columns.
LOOKUP Formula in Excel (Table of Contents)
Excel LOOKUP Formula
This LOOKUP formula is the most basic excel function used to perform the search. Lookup functionality in Excel enables the user to perform an approximate match in a specific row range or column range or from an array and returns the value for the same position from another column or row.
Explanation of LOOKUP Formula in Excel
Excel Lookup function has two types of syntax:- vector and array
#1 – LOOKUP(lookup_value,lookup_vector,[result_vector])
The above syntax for vector form of lookup refers to a single data column or row range to search for the value and return the exact or the closest value from the same position in a different row or column range.
- Lookup_value: This is the mandatory argument, in which value to be searched is entered. The value can be numeric, text or the reference cell where the value to be searched is present.
- Lookup_vector: This is the mandatory argument, in which a single column or row range is to be given where the value has to be searched. Moreover, the given range should be in ascending order only, else the lookup may not return the correct value.
- [Result_vector]: This is an optional field, in which a single column or row range is given with the same size as lookup_vector. The function returns the corresponding value from the second range in the same position as the value to be searched in the range entered in the lookup_vector argument. If this argument is left blank then the excel lookup function will return only the value to be searched in the range entered in lookup_vector.
#2 – LOOKUP(lookup_value,array)
The Excel lookup function for array searches the specific value in the first column or row and returns the corresponding match from the same position in the last column or row of the given array range.
- Lookup_value: This is the required field in the function and the value to be searched in the array has to be given.
- Array: This is a mandatory argument where the range of the array which consists of the value to be searched and the corresponding value to be returned.
How to Use Lookup Formula in Excel? (with Examples)
Following are the examples of LOOKUP Formula in Excel.
Below is the data table with account details, where the excel lookup formula is applied for the fields entered by column.
Step 1: As we can see the account numbers are not in ascending order, this is the mandatory step before applying the excel lookup formula. So let us sort the first column in ascending order.
Step 2: Now let us apply an excel lookup formula with lookup_value for vector type and understand what does the lookup functions retrieve in return.
Excel LOOKUP Formula returns the result as Travel.
As seen in the above formula applied in cell G2, the account number range “A1: A6” is entered in lookup vector argument and the account description range “B1: B6” is entered in result value argument in which the function pulls the corresponding value.
Step 3: Similarly by giving the value for account description, let us enter the reference cell to retrieve the account number.
As can be seen in the above screenshot, the lookup_value is the reference cell G2 and the value is searched in column “B1: B6” is given and the corresponding value is retrieved from column “A1: A6”.
Now consider the below data table in a horizontal format, here the range in lookup formula will be given by rows.
Now applying the excel lookup formula to retrieve the account description field, we enter the account number as the lookup value.
As can be seen in the above screenshot, the lookup value is given a value from the reference cell “I2”, whereby the lookup vector and the result value has been switched to low range. The value to be searched is given in lookup vector range “A1: F1” and the corresponding value is retrieved from “A2: F2” row range.
Consider the below table to understand the lookup formula for array format.
Now applying the excel lookup array formula, with the account number in the first column and the amount in USD in the last column. We can use the excel lookup formula to search the account number and pull the matching value from the amount in USD field as shown below.
As can be seen in the above screenshot in cell G3, the value to search is given as “G2” and the range of array for data table range “A1: D6” is given in the second argument. This enables the formula to locate the corresponding value in the last column for the value in the first column.
Considering the below example which will explain to us how the smallest value would be located in the exact matching value in the column range given in lookup vector is not found and pull the value from the range given in the resulting vector.
Now the excel lookup formula for type array is applied to pull the respective data as shown below.
As seen in the above screenshot, in the array type lookup formula in excel the exact value does not exist in the first column account number field. Thereby the lookup formula takes the next smaller value in the data available in the given column. In this case, the next smaller value after “81000 ”existing is “57190” which is the largest number in the smallest values. Hence the corresponding value from the last column is pulled by the lookup formula.
Now similar lookup formula in excel can be entered for the vector type as shown below.
As seen in the above image, vector type lookup formula in excel is applied in cell G4, thereby the same concept is applied as explained for the array type. Hence the next smaller value for cell G3 is identified from the first column and the corresponding value from the last column is pulled by the lookup formula.
Things to Remember
- The data range provided in the lookup vector argument should be in ascending order. Else not sorting the data in the stated manner may result in an incorrect value.
- The column or row range entered in lookup_vector and result_vector should be of the same size.
- The way in which the lookup performs is it does an approximate match in the column or row range provided in the lookup_vector argument and if the exact value to be searched does not exist in the column range then it will search the next smallest value in that specific single column or the largest value in the lookup_vector range which is smaller than the lookup_value.
- If in case the value entered in the lookup_value is smaller than the smallest value in the lookup_vector range then the lookup function returns an “#N/A” error value.
- The lookup formula in excel is not case sensitive and thereby does not consider uppercase and lowercase texts.
This has been a guide to Excel LOOKUP Formula. Here we discuss how to use LOOKUP Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –