# vlookup backwards

#### rcirone

##### Active Member
Can I do a vlookup backwards on a row

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
No, but you can use INDEX() and MATCH() to do the same thing. See this article.

Can a VLOOKUP backwards on a row ?

The answer is, Yes ! , but Not Recommended !!

Example, from the following table , if the looking up item is "Jacket",

A…………B
Price……Item
10……….Sweater
25……….Jacket
30……….Pants

Then the "Price" for "Jacket" formula is :

1] Using VLOOKUP function

=VLOOKUP("Jacket",IF({1,0},B2:B4,A2:A4),2,0)

or

=VLOOKUP("Jacket",CHOOSE({1,2},B2:B4,A2:A4),2,0)

2] Using LOOKUP function

=LOOKUP(2,1/(B2:B4="Jacket"),A2:A4)

3] Using INDEX/MATCH function

=INDEX(A2:A4,MATCH("Jacket",B2:B4,0),0)

The INDEX/MATCH solution will be the best choice

Regards
Bosco

I always wanted to learn more about =CHOOSE() and I am still struggling This is one of the formula I am yet to grasp the usage logic for it.

Hi Bosco, hope you are doing well. Could you please explain the usage of CHOOSE() in your above formula ? It would be great help

=CHOOSE({1,2},B2:B4,A2:A4) will return an array consisting of B2:B4 followed by A2:A4. In other words, by using CHOOSE() he reversed the order of the columns so that VLOOKUP can look at B and pick from A.

To make it more clear, look at this example:
Excel Workbook
ABCD
1A11
2B2
3C3
4D4
5E5
6F6
Sheet1

Click on the formula bar and hit F9, and the CHOOSE() is actually returning:
{1,"A";2,"B";3,"C";4,"D";5,"E";6,"F"}

So now you can VLOOKUP 3 and return "C", rather than vice-versa.

I always wanted to learn more about =CHOOSE() and I am still struggling This is one of the formula I am yet to grasp the usage logic for it.

Hi Bosco, hope you are doing well. Could you please explain the usage of CHOOSE() in your above formula ? It would be great help

1] The normal used of VLOOKUP function is :

=VLOOKUP("Jacket",A2:B4,2,0)

=VLOOKUP("Jacket",{10,"Sweater";25,"Jacket";30,"Pants"},2,0)

=#N/A

Which return #N/A because of the wrong columns order

2] The CHOOSE function can reverse the columns order

=CHOOSE({1,2},B2:B4,A2:A4)
>>
={"Sweater",10;"Jacket",25;"Pants",30}

3] The VLOOKUP/CHOOSE combines can produce the correct columns order

=VLOOKUP("Jacket",CHOOSE({1,2},B2:B4,A2:A4),2,0)
>>
=VLOOKUP("Jacket",{10,"Sweater";25,"Jacket";30,"Pants"},2,0)
>>
=25

Regards
Bosco

Perfect explanation, Sal & Bosco

Ok, while writing CHOOSE(), I noticed the precedence of the elements in the array and I do understand that it matters the most for the reversal of these columns, correct ?

Also, could I swap columns by using CHOOSE() rather than reversing them ? Is it possible ? Just asking these because I do not want to miss out on any rules while learning this formula.

One last thing, CHOOSE() is used only to reverse column order and is there anything else in the whole of Excel for which CHOOSE() can be used or implemented ?

Thanks a bunch, dear friends I appreciate your efforts to help !!!!

Regarding how CHOOSE increat a range of the cells in reverse order.

Please enter table A1:C4 as below

A…………B……………C
Price……Item………..Code
10……….Sweater……S125
25……….Jacket……..J25
30……….Pants………P24

1] Enter formula in E2,

=CHOOSE({1,2},B2:B4,A2:A4)

2] Select E2:F4, array enter in confirm of press Ctrl+Shift+Enter

3] The results returned as below

Sweater……10
Jacke………25
Pants………30

4] Enter formula in H2,

=CHOOSE({1,2},B2:B4,A2:A4)

5] Select H2:J4, array enter in confirm of press Ctrl+Shift+Enter

6] The results returned as below

Sweater……S125…….10
Jacket……..J25……….25
Pants………P24……...30

Regards
Bosco

Replies
7
Views
178
Replies
2
Views
71
Replies
4
Views
126
Replies
1
Views
110
Replies
4
Views
171

1,203,082
Messages
6,053,417
Members
444,662
Latest member
AaronPMH

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