VLOOKUP

[waynered]

Can anybody explain VLOOKUP function and it's parameters.

Thanks

 

[dk]

OK,

Consider this:-

Book3
	A	B	C	D	E	F
2
3	This is your data - called Table Array.
4	Name	Age		Enter name >>>	Dan	<<< This is your Lookup Value
5	Dan	27
6	Jon	30		Formula here >>>	27	<<< This formula looks up the persons
7	Ben	25				age based on what's in cell E4.
8	Paul	22
9	Mark	31
...

The purpose of the VLOOKUP function in cell E6 is to return a person's age, based on their name (entered in cell E4). The VLOOKUP formula looks like this:-

=VLOOKUP(E4,A5:B9,2,FALSE)

The first argument (parameter) is the Lookup Value. This is the value we're going to search for in the data table.

The second argument (A5:B9) defines our data table - this is called Table Array by Excel. The values you're searching for must appear in the first column in this range.

The third argument tells Excel how many columns to count across in the range specified in the second argument. In this example column A is 1, column B is 2.

The last argument tells Excel whether to find an exact match (FALSE) or to find the closest match (TRUE). I personally always use FALSE but TRUE is quicker is your data is sorted and you want to find the closest match, if an exact match is not found.

So, in summary -

Take the lookup value in E4 (Dan), go and look in the Table Array. Search for Dan in the first column. If found, move to the second column. Return that value - 27.

I hope that's clear. I was trying to explain it to some of my colleagues recently and it can be a bit of a bugger to understand However, once you've got it, I think it's one of the most useful and powerful functions that Excel offers.


_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-22 07:32

 

[Aladin Akyurek]

& you can replace FALSE with 0, which you often see at this site.