How to Lookup a Table in Excel (8 Methods)

From a large dataset whenever you want to fetch or look up a particular value then you will need to know how to lookup the table (dataset) in Excel. In this article, I’m going to explain with examples how you can use Excel lookup table.

To make the explanation understandable, I’m going to use a dataset of sales information for a particular region. The dataset contains 3 columns. These are SalesPerson, Region, and Sales.

What is Lookup Table?

From a large dataset when we fetch any value associated with the dataset then we treat that dataset as a lookup table. Here, we can use the total range of the dataset, or we can name the range which is ultimately considered as a table.
In short lookup tables in Excel is named table which we use with the LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, INDEX & MATCH types of lookup functions to find any data associated with the range of dataset or the table.
When we use any table name then instead of giving a reference, we can use the table name to lookup any value. That’s why such a type of table is known as a lookup table.

How to Lookup a Table in Excel: 8 Ways

1. Using Excel LOOKUP Array to Lookup a Table

You can use the LOOKUP function to lookup table in Excel. There are two ways to do it depending on your dataset and need.

Here, I’m going to show you the Array form of using the LOOKUP function.

Array form: When you have a table or something like that in Excel you can use the array form because an array is a collection of values in rows and columns that you want to search. It looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.

To start with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

Here, in the LOOKUP function, I selected the cell F4 as lookup_value and selected the range B4:D12 as array.

Now, the LOOKUP function will look for the corresponding values of the lookup_value and will return a value from the same position in the last row or column of the array associated with the lookup_value.

You can use array form of LOOKUP when the values that you want to match are in the first row or column of the array.
Always remember to place the values in ascending order in an array while using the LOOKUP function.

⏩ Press the ENTER key and you will get the Sales information of the lookup value Adam Smith.

Read More: Different Types of Lookup to Apply in Excel

2. Using LOOKUP Vector to Lookup in a Table

Now, I’m going to show you the use of vector form of the LOOKUP function to lookup table in Excel.

Vector form is used to search one row or one column for a value. If you want to specify the range that contains the values that you want to match, then you can use the vector form.

To start with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

Here, in the LOOKUP function, I selected the cell F4 as lookup_value and selected the range B4:B12 as lookup_vector then selected the range D4:D12 as result_vector.

Now, the LOOKUP function will look for the corresponding values of the lookup_value and will return a value from the result_vector.

⏩ Press the ENTER key and you will get the Sales information of the lookup value, Adam Smith.

Read More: How to Lookup with Multiple Criteria in Excel

3. Lookup Excel Table with Range

If you want, you can lookup values from another table or range of datasets.

Here, I’m going to use an external lookup table to fetch the value. To demonstrate the procedure, I’m using the dataset given below. In the dataset, I want to look up the corresponding Grade depending on the Sales value. Where I predefined the Grade with the Sales range.

To start with, select any cell to place your resultant value.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

Here, in the LOOKUP function, I selected the cell D4 as lookup_value and selected the cell range $G$3:$H$7 as lookup_vector (I used absolute reference to use the formula later for the rest of the cells).

Now, the LOOKUP function will look for the exact or closest value of the D4 cell in the lookup table.

⏩ Press the ENTER key and you will get the corresponding Grade based on the Sales value.

Here, the Grade is C for the value $4567. In my lookup table, there is no grade for the exact $4567 value, yet the LOOKUP function assigned a grade because it searches for the nearest value. The lookup will start to look for the value $4567 and till $5000 and its grade as C, that is why it will show Grade C.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Lookup Text in Excel

4. Lookup Excel Table with Named Range

Earlier I used the range of lookup table now, I’ll show you how you can use the named lookup table.

To start with, select the lookup table range to name the table.

➤ I named the lookup table range Ratings.

Now, select any cell to place your resultant value.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

Here, in the LOOKUP function, I selected the cell D4 as lookup_value and used lookup table name Ratings as lookup_vector.

Now, the LOOKUP function will look for the exact or closest value of the D4 cell in the lookup table.

⏩ Press the ENTER key and you will get the corresponding Grade based on the Sales value.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Lookup Multiple Values in Excel

5. Using VLOOKUP to Lookup Table

By using the VLOOKUP function you can fetch values from the lookup table. The VLOOKUP function is used to lookup data in a table organized vertically.

To start with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

Here, in the VLOOKUP function, I selected the F4 cell as lookup_value, B4:D12 as table_array, 3 as col_index_num, FALSE as range_lookup where (FALSE means Exact Match).

Now, the VLOOKUP function will return the Sales of F4 cell from the table range as it is the 3rd column.

⏩ Press the ENTER key and you will get the sales information for the SalesPerson “Rachel Ross”.

Read More:  How to Lookup Value from Another Sheet in Excel

6. Using HLOOKUP to Lookup Table

You can use the HLOOKUP function when you want to find values located in a row across the top of a lookup table of data, also you want to look down a specified number of rows.

The HLOOKUP function searches for a value in the top row of a table or an array of values then it returns a value in the same column from a row you specify in the table or array.

To demonstrate to you the procedure, I’m going to use the dataset given below.

To start with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

You also can use the following formula both will give you the same result.

Here, in the HLOOKUP function, I used “Sales” as lookup_value, B4:D12 as table_array, 3 as row_index_num, FALSE as range_lookup where FALSE means Exact Match.

Now, the HLOOKUP function will return the Sales of row 3.

⏩ Press the ENTER key and you will get the sales information of 3rd Row.

Instead of typing the first-row value you also can use the cell reference.

To start with, select any cell to place your resultant value.

➤ I selected the G5 cell.

⏩ In cell G5, type the following formula.

You also can use the following formula both will give you the same result.

Here, in the HLOOKUP function, I selected the F5 cell as lookup_value, B4:D12 as table_array, 3 as row_index_num.

Now, the HLOOKUP function will return the Region of row 3 (according to the dataset, not the sheet).

⏩ Press the ENTER key and you will get the Region information of 3rd Row.

Read More: How to Use VBA Lookup Function in Excel

7.  Using XLOOKUP to Lookup Table

The XLOOKUP function is the improved version to lookup value from a lookup table. This function works in any direction. This function is available on Microsoft 365

The XLOOKUP function searches a range or an array and then returns the item associated to the first match, it finds the lookup value regardless of which side the return column is on. It can return an array with multiple items. If no match exists, then XLOOKUP can return the closest (approximate) match.

To begin with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

Here, in the XLOOKUP function, I selected the F4 cell as lookup_value, B4:B12 as lookup_array, D4:D12 as return_array, “Not Found” as if_not_found, 0 as match_mode.

Now, the XLOOKUP function will return the associated result of the lookup_value from the return_array.

⏩ Press the ENTER key and you will get the Sales information for the SalesPerson “Kristin Moran”.

8. Using INDEX-MATCH Formula to Lookup Table

If you want to fetch data from anywhere to create a lookup table you can use the INDEX function and the MATCH function.

To begin with, select any cell to place your resultant value.

➤ I selected the G4 cell.

⏩ In cell G4, type the following formula.

Here, in the INDEX function, I selected the cell range D4:D12 as an array,  MATCH(F4,B4:B12,0) as row_num.

Next, in the MATCH function, I selected the F4 cell as lookup_value, B4:B12 as table_array, 0 as match_type where (0 means Exact Match).

⏩ Press the ENTER key and you will get the Sales information for the SalesPerson “Jim Carry”.

Things to Remember

🔺 Always remember to place the values in ascending order in an array while using the LOOKUP function. Otherwise, it will show a #N/A error.

🔺 The lookup should be the same as in the main table in excel.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Download to Practice

Conclusion

In this article, I have explained 8 ways to use the Excel lookup table. I also tried to cover, when and why the errors may come frequently while using different lookup functions. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.