Pinterest Pixel

Vlookup in an Excel Table

John Michaloudis
What does it do?

What does it do?

Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.

Formula breakdown:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it means:

=VLOOKUP(this value, TableName, and get me value in this column, Exact Match/FALSE/0])

Excel Tables are just amazing and should be used all the time, whether you have 2 rows or 200,000 rows of data!

You can read the benefits of using an Excel Table here:

Excel Tables

When you use a Vlookup formula to lookup in an Excel Table then your formula becomes dynamic due to its structured referencing.

What that means is that as the Excel Table expands with more data added to it, your Vlookup formula’s 2nd argument (table_array) does not need to be updated as it refers to the Excel Table as a whole by referring to its name eg Table1  or Table2  or Table3  etc

In the example below our Excel Table name is Table2 and as we add more rows of data to it, the Vlookup formula does not need to be adjusted.  How bloody cool is that?

Vlookup in an Excel Table | MyExcelOnline
Download workbookVlookup_Excel-Tablev2.xlsx

STEP 1: We need to convert the data into an Excel Table. Press Ctrl + T then press OK.

Vlookup in an Excel Table

STEP 2: Now let us create the formula to get the price of the Laptop. Let us use the VLOOKUP formula:

=VLOOKUP(G15, Table1, 2, FALSE)

This will get the lookup value (Laptop in Cell G15), then search in the first column of Table1.

Afterwards it will get the value in Column #2 which is the price. The FALSE means is we want to get the exact match.

Vlookup in an Excel Table

STEP 3: Drag down the formula to copy it across the table. Notice that the second row is looking for the price of Mouse. This does not exist in our data table yet.

Vlookup in an Excel Table

STEP 4: Now add and type in a new row in our table for the price of the mouse.

Vlookup in an Excel Table

The beauty with this is our VLOOKUP formula still works fine. Since we are using the Table1, there is no need to update the range of values that our VLOOKUP will use. It is now automatically included and the price of the mouse is retrieved right away.Vlookup in an Excel Table

 

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Excel Table Headers Show in Columns

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...