# vlookup

#### caprichoo

##### Board Regular
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?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

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

one more thing fairwinds,

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

Thanks a lot

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)

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.

Replies
1
Views
241
Replies
1
Views
576
Replies
1
Views
227
Replies
2
Views
113
Replies
5
Views
263

1,214,264
Messages
6,118,560
Members
448,837
Latest member
Josephjet

### 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.

### Which adblocker are you using?

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

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