Forum to talk about all the latest technologies - ins and outs!!
Posts: 317
Joined: Tue Sep 25, 2007 12:41 pm

VLOOKUP or 'vertical lookup' is one of Excel's frequently used lookup and reference functions. In general, it looks for a 'value' you want to look up from a table or a range, and when found, returns that value.

VLOOKUP function has four parameters or arguments in its syntax. The parameters are separated by commas.

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

lookup_value: the value you want to look up or searching for

table_array: the range within which your specified value is located

col_index_num: this is the column number in table_array that contains the return value.

range_lookup: here you specify the logical argument TRUE or FALSE. If you specify TRUE, it means you are looking for an approximate match for your return value. But if you specify FALSE, that means you want an exact match for your return value.

In the following example, in my Excel sheet named Solar, I have infos about different solar panels, number of cells and unit price. I have only 4 models here just to show you how VLOOKUP Function works. In reality you could have hundreds of data.

Now, in my Excel sheet 1, I want to show infos about cell numbers (in Column B) and unit prices (in Column C) when I enter the model number in Column A.

So in Cell B2 in Sheet1 type

=VLOOKUP(A2;Solar!\$A\$3:\$D\$6;3;FALSE)

It means, when you type the model number in Cell A2, it looks for the model info within the table in sheet Solar where you have fixed the range (A3:D6) with absolute reference using the '\$' sign (\$A\$3:\$D\$6), then goes to Column 3 which contains the return value (in this case, number of cells). As the 4th parameter is FALSE, so it returns you the exact match it finds in Column 3.

Now once you finished typing the above formula in cell B2, press Enter. You will see #N/A as you haven't entered any model number in Cell A2. Now copy the formula down the column B as per your requirement. It will be filled with #N/A. Don't worry about it. We will make it clean soon

Now in Column C of your Sheet 1 you want to show the unit price as well, when you enter the model number in Cell A2. So like in Cell B2, type the following in Cell C2

=VLOOKUP(A2;Solar!\$A\$3:\$D\$6;4;FALSE)

Look, here the Column number is 4, as the 4th Column in Sheet Solar contains the return value for unit price. Now copy the formula down the Column C as per your requirement. Again it will be filled with #N/A. Don't worry

How to avoid the #N/A error value

So when the VLOOKUP Function doesn't find the exact number or match to return, it returns you the #N/A error value. It makes your Excel sheet clumsy. To avoid this, you can wrap the VLOOKUP Function within the IFERROR Function.

So enter in Cell B2

=IFERROR(VLOOKUP(A2;Solar!\$A\$3:\$D\$6;3;FALSE);"")

Copy it down the column. You will have a clean column without the #N/A errors.

Do the same for the prices in Cell C2

=IFERROR(VLOOKUP(A2;Solar!\$A\$3:\$D\$6;4;FALSE);"")

Again, copy it down the column to avoid the #N/A errors.

Take care of the column numbers in the formula. In cell B2 it is 3, in cell C2 it is 4.

Now in any cell in Column A you enter your model number, corresponding solar cells numbers and prices are shown in Column B and Column C.

Have fun

Cheers!!!

*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.