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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
54,365
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,133
Messages
5,835,594
Members
430,369
Latest member
pingel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top