How to use the VLOOKUP Function - Advanced Part 3

Friends,

I hope you have read my previous article regarding VLOOKUP. In this post I'll give you some more information about nested VLOOKUP step-by-step.

Nested is to put a function within another function (I'll discuss in detail about NESTED FUNCTION in some another post).

I'll show you nested function in VLOOKUP today. 

Click this link to download Excel FIle : Vlookup Data File

Please note the red colored formula in the below picture.

Nested VLOOKUP in Excel with COLUMNS fnction and Named Table

As you can see in my above formula, "=VLOOKUP(G5,Data1,COLUMNS(A5:E5)-2,0)" in column index number I've written another function "COLUMNS()", this is an example of nested function.

You can put COLUMN() function and upto 64 functions can be nested as per requirement.

This is to calculate the column index number, either you can put numbers like 2,3,4,5 etc., or you can use formula. I'll show you another method which I generally use. Have a look at the below image circled in Blue.

Nested VLOOKUP in Excel with COLUMNS fnction

I put column number (circled in blue) to the top of the column name, and then I refer the column index number by that number and drag it to the right as per requirement, then fridge it by pressing 'F4' button once. (Here I again changed my table name to 'Data2').
It will make the VLOOKUP very dynamic.

But have you noticed Table Array part, it is 'Data1' or 'Data2'....

What is this? Where did I get it?

How to name a table in Excel

Its the table name, Data1. You can put a name to excel table. For detail view visit my Youtube Video link.

Youtube Link - VLOOKUP advanced - 2

How ? Simple Select the range as per your requirement in excel, go to the box which displays Cell Number, type the table name, that's it. Done...

So, a table array can also be refereed by table name. I will discuss about the naming an Excel table in another article saperately.

I'll show you few easy method to calculate VLOOKUP in my next blog....

Till then, keep reading, like & share my Youtube videos.
Thank you....