Excel formula creating help - lookup

h2009

h2009

h2009

Associate
Joined
6 Jan 2008
Posts
316
Hi there, Im trying to find the formula which will look a lookup and return a result i.e. heres a simple example:

Code: 
Row A                Row B	   Row C

bulb 1                0.152	   0.227
bulb 2                0.149	   0.224
bulb 3                0.034	   0.051
bulb 4                0.148	   0.222
bulb 5                0.022	   0.033

Row A                Row  B                                           Row C   Row D
Bulb cost  ----->  (i want to type in bulb X) > returns with	   0.034	0.051

Is it possible to make a formula which can do this?

thank you
 
If I understand correctly, VLOOKUP will do this. It's a bit like this (off the top of my head) - =VLOOKUP([text to look for],[range],[which column in the range do you want it to return when it finds a result?],[closest (TRUE) / exact (FALSE) matches only?])

So here, where you have bulb 1 in A1 and bulb 5 in A5, and (I want to type in bulb x) is in B8, try the following formula in C8:

=VLOOKUP(B8,A$1:C$5,2,FALSE)

Make sure you fix the range with $ signs, so if you fill down, it still works. If you want it to return what is in column C in the original range, change the 2 to a 3 (i.e. the third column of the range rather than the second). So in D8 you have:
If I understand correctly, VLOOKUP will do this. It's a bit like this (off the top of my head) - =VLOOKUP([text to look for],[range],[which column in the range do you want it to return when it finds a result?],[closest (TRUE) / exact (FALSE) matches only?])

So here, where you have bulb 1 in A1 and bulb 5 in A5, and (I want to type in bulb x) is in B8, try the following formula in C8:

=VLOOKUP(B8,A$1:C$5,3,FALSE)

Hope this helps!
 
Thank you for your help just what i needed!

Just another quick question is it possible i to match the lookup cell (B8) -
i.e. instead of typing 'bulb 3' i could type '3' - without changing the name of the cell

so if i have the countries albania, algeria - and by typing alb (i.e. looks up intelligently) and returns albania?
 
You must log in or register to reply here.
Back
Top Bottom