vlookup

[caprichoo]

Hi,

I have a question on double vlookup. I have try to search this forum for any related info but dun quite find what I am looking for. My problem is like this

0|__A_____B____C_______D
1|_____<50___51-60____>61
2|_AA__100___101______102
3|_BB__103___104______105
4|_CC__106___107______108

so what I need to do is I need to return the value for AA with 53, that is it will return the value of 101. Another instance is when I need CC with 65, it will return 108. How do I do a double lookup so that I can look at the column and pick up whether AA,BB or CC and then look at row 1 to see whether it is below 50, between 51 and 60 or more than 61?

Please Help...Thanks in advance

 

[fairwinds]

Hi,

Try;

=VLOOKUP(F2,A2:D4,MATCH(F3,B1:D1)+1,0)

Note that I changed the headers.

Book1
	A	B	C	D	E	F
1		0	51	61
2	AA	100	101	102		CC
3	BB	103	104	105		65
4	CC	106	107	108		108
Sheet4

 

[caprichoo]

Thanks Man, You are great.
I didn't know that you can actually change the range and still can do the range lookup.....

Thanks a lot

 

[caprichoo]

one more thing fairwinds,

Could you enlighten me on the match function? why is there a need to use the +1?

Thanks a lot

 

[Peter_SSs]

caprichoo

If you wanted to keep your headers, and using fairwinds layout (and formula slightly modified), you could use:
=VLOOKUP(F2,A2:D4,MATCH(F3,{0,51,61})+1,0)

Of course this would not lend itself so well if there were lots of columns with different range headings, but for only 3 I think is OK.

If it is possible that F3 could be negative, then fairwinds formula, and my modification, will both return #N/A. In this case, you could try:
=VLOOKUP(F2,A2:D4,CHOOSE(2+(F3>60)-(F3<=50),2,3,4),0)

 

[fairwinds]

one more thing fairwinds,

Could you enlighten me on the match function? why is there a need to use the +1?

Thanks a lot

As the range in VLOOKUP formula is A2:D4 and the match is performed on B1:D1 i.e one column offset, I need to compensate by +1.