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?

DOWNLOAD WORKBOOK

Vlookup_Excel Table

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Advanced SUMPRODUCT Function: Sum Multiple Criteri... What does it do? It returns the sum of multiple criteria from the corresponding ranges or arrays Formula breakdown: =SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values) What it means: =SUMPRODUCT((find my criteria in this array) * (find my criteri...
Evaluate Formulas Step By Step in Excel This is one of the coolest tricks I have seen in Excel, as there are countless times wherein I had a hard time understand formulas. Especially long and complex ones! Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it! ...
Autosum an Array of Data in Excel When you have an array of data in Excel with Totals at the bottom and to the right of the data, you can quickly fill in the Totals with the Autosum button. STEP 1: Highlight your data including the "Totals" row and column; STEP 2: Click the Autosum button (under the Home or...
Highlight All Excel Formula Cells   Whenever you are auditing an Excel worksheet and need to know where all the formulas are located, a great way is to highlight the formula cells in a distinctive color.  This is how it is done: STEP 1: Select all the cells in your Excel worksheet by clicking on the to...