vlookup backwards

[rcirone]

Can I do a vlookup backwards on a row

 

[Sal Paradise]

No, but you can use INDEX() and MATCH() to do the same thing. See this article.

 

[shemayisroel]

Following from Sal, your unable to place a -3, -4 etc on the col_index_num but an INDEX/MATCH solution will be perfect for it.

See also

http://www.contextures.com/xlFunctions03.html

 

[bosco_yip]

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

 

[Stormseed]

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

 

[Sal Paradise]

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

 

[Sal Paradise]

To make it more clear, look at this example:

Excel Workbook
	A	B	C	D
1	A	1		1
2	B	2
3	C	3
4	D	4
5	E	5
6	F	6
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.

 

[bosco_yip]

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

 

[Stormseed]

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

 

[bosco_yip]

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