vlookup

caprichoo

Board Regular
Joined
Nov 2, 2005
Messages
53
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try;

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

Note that I changed the headers.
Book1
ABCDEF
105161
2AA100101102CC
3BB10310410565
4CC106107108108
Sheet4
 

caprichoo

Board Regular
Joined
Nov 2, 2005
Messages
53
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

Board Regular
Joined
Nov 2, 2005
Messages
53
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,470
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
caprichoo said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,030
Messages
5,569,757
Members
412,289
Latest member
Kingchaos64
Top