vlookup help

  • Thread starter Thread starter valerie
  • Start date Start date
V

valerie

Im still uncertain of how to go about inserting my data in lookup_value,
table array, etc found in Vlookup function even after reading the help
built in to excel. Could anyone provide an example of inserting various
values in Vlookup function?
Thanks for any help.
 
Hi

An Example:
You have a lookup table on Sheet1 with lookup values in range C2:F100 (Val1,
Val2, Val3, Val4), where column C (Val1) is key column (identifier). NB! The
key column MUST always be leftmost in lookup table!
On another (or on same) sheet you have some key value in cell X2, and you
want according Val3 in cell Y2
Y2=VLOOKUP(X2,C2:F100,3,FALSE)

The number 3 makes the formula to return the according value from 3'd column
in lookup table. The FALSE parameter makes it to search for exact match -
when no key value in lookup table matched, #N/A is returned. You can use
also parameter TRUE, but only when lookup table is ordered by key column -
then the nearest match is returned. With non-ordered lookup table and TRUE
parameter the result is wrong in general (with TRUE, some result is always
returned).


Arvi Laanemets
 
valerie said:
Im still uncertain of how to go about inserting my data in lookup_value,
table array, etc found in Vlookup function even after reading the help
built in to excel. Could anyone provide an example of inserting various
values in Vlookup function?
Thanks for any help.
Click to expand...

A simple example:
Suppose you have the letters q,w,e,r,t in cells A1:A5 and the numbers
1,2,3,4,5 in B1:B5. Then A1:B5 is your table array.
The formula to get the number corresponding to the letter e is:
=VLOOKUP("e",A1:B5,2,FALSE)
You have to put the double quotes around e because it is text.
Alternatively, suppose you have the letter e in cell D9 (this is your lookup
value):
=VLOOKUP(D9,A1:B5,2,FALSE)
In both these formulas, the number 2 is because you want to return data from
the second column of the table array. The FALSE is because your letters
q,w,e,r,t are not in sorted order, so you must be looking for an exact
match.

By contrast, suppose you had numbers 1,3,5,7,9 in E3:E7 and letters in
G3:G7. To look up the letter corresponding to a number in D12, you would
use:
=VLOOKUP(D12,E3:G7,3,FALSE)
or
=VLOOKUP(D12,E3:G7,3,TRUE)
If the number in D12 is 1,3,5,7 or 9, these give the same result. If it is
not (e.g. 4), the first will return #N/A as an exact match is not available)
whereas the second will look at the nearest match, which is 3.

Hope this helps. Post back if you have a specific query.
 
Hey, trillion thanks to ya all. You all have done a good job, esp paul
with his elaborative example.
 
Back
Top