Using Data Validation and VLOOKUP in Excel TIP

 

Doesn’t it get redundant when you use VLOOKUP function in Excel with a large data table  and you have to type in the Lookup value each time you need a new one to look up? 

Try using Data Validation List option in Excel along with your VLOOKUP function.

For this tip I am using a sample data table that lists SKU data information.  Let’s say this is a very large table and I need to look up SKU specific number information. 

The headings from the table has been typed starting with cell I2:M2.

1.      In cell I2, I typed  the function =VLOOKUP(Table1[@SKU],Table1[#All],1)

2.      Select the Data tab and choose the Data Validation option. Select Data Validation from the list.

You should see the following as shown in this window:

3.      Choose List from the Allow drop down menu.  Select the SKU list A2:A19.

4.      Now you can repeat the VLOOKUP formula for each column heading.  With the drop down you can now select the SKU number and populate all the columns with the correct data from your lookup table.