How to Create a Lookup Table in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on September 17, 2023

This tutorial demonstrates how to create a lookup table in Excel and Google Sheets.

lookup tables intro

Table Source Data

Say you have a table like the one below.

lookup tables data

The data is set up as a searchable database, with the data is stored in columns – called field names, and in rows – called records. Each row holds the data for an individual Product. Setting out information in Excel in such a way enables you to sort and filter on the data, as well as use functions such as VLOOKUP. Your database can contain as many columns and rows available in a single Excel sheet (16,384 columns and 1,048,576 rows are available in each sheet but that might be a bit large for Excel to cope with!)


A lookup table enables you to fetch data from your existing table or range of data using the VLOOKUP Function.

  1. In cell G3, type in the name of the Product to look up the Qty in Stock and Price for.

lookup tables lookup word

  1. In cell H3, type in a VLOOKUP formula to get the Qty in Stock for that Product. Type the formula:

lookup tables vlookup

  1. Repeat the process to look up the Price of the Product.

lookup tables vlookup price

  1. When you type in a different Product, the values in H3 and I3 change accordingly.

lookup tables change lookup

Drop-Down List

You can also create a drop-down list with data validation to choose a Product from a list instead of typing in the product’s name.

  1. Click in the cell where you wish to insert the drop-down list, and then, in the Ribbon, go to Data > Data Tools > Data Validation > Data Validation.

lookup tables ribbon data validation

  1. You can then choose List and select the range for the drop-down list. You can also customize the input message and error alert if you wish.

lookup tables data validation

  1. Click OK to create the drop-down list.

lookup tables dropdown list

  1. Choose a different Product from the list to look up the Qty and Price from the original table data.

lookup tables change dropdown

Lookup Table in Google Sheets

You can use the VLOOKUP Function to look up values in your table in Google Sheets as you do in Excel, with or without a drop-down list data validation cell.

lookup tables gs vlookup

AI Formula Generator

Try for Free

See all How-To Articles