vlookup choice from 2 left hand columns

[mistersteve]

Hello all,
Please forgive me if I don't explain properly
Using vlookup from another spreadsheet
I select from the table array as below and select column C as result

However, there are in some instances there is a part number in column B
Therefore, can it be possible to ignore Column A as first left column and choose Column B whenever there is a part number present

A B C
Group Part Number Discount
1 P302 15
2 P311 40
3 P179 25
4 P179 12345 57.62

Hoping this can be answered and thankyou in advance

 

[Fluff]

How about

=XLOOKUP(A2,Sheet1!$B$2:$B$20,Sheet1!$C$2:$C$20,XLOOKUP(A2, Sheet1!$A$2:$A$20,Sheet1!$C$2:$C$20,"",0),0)

 

[mistersteve]

How about

=XLOOKUP(A2,Sheet1!$B$2:$B$20,Sheet1!$C$2:$C$20,XLOOKUP(A2, Sheet1!$A$2:$A$20,Sheet1!$C$2:$C$20,"",0),0)

Thank you Fluff, unfortunately i don't have XLOOKUP on office 2016,
is there another way i could use

 

[alansidman]

You could use this:

=vlookup(X,choose({1,2},B:B,A:A),2,0) where X is your criteria, your data is in columns A and B and the match is in Column B and the needed data is in column A.​

 

[Fluff]

unfortunately i don't have XLOOKUP on office 2016,

Then why are you showing 365 in your profile, if your using 2016?

 

[Alex Blakenburg]

This should give the same result as Fluff's Xlookup.
It assumes the value to lookup is in A2 that is on the same sheet as the formula.

=IFERROR(VLOOKUP($A2,Sheet1!$B$2:$C$20,2,FALSE),
       IFERROR(VLOOKUP($A2,Sheet1!$A$2:$C$20,3,FALSE),
          ""))

If anyone else want to try here is the data in XL2BB

20210730 Vlookup nested.xlsx
	A	B	C	D
1	Group	Part Number	Discount
2	P302		15
3	P311		40
4	P179		25
5	P179	12345	57.62
6
Sheet1

 

[mistersteve]

Hi Alex
is it me? because I keep getting the answer of 25 and not 57.62

 

[mistersteve]

Then why are you showing 365 in your profile, if your using 2016?

I have 365 at home, but i am currently in work, they only have 2016

 

[Fluff]

In that case I would suggest you change your profile to show 2016 & when a 365 solution is ok say so in your post.

Alex's formula works for me.

 

[mistersteve]

In that case I would suggest you change your profile to show 2016 & when a 365 solution is ok say so in your post.

Alex's formula works for me.

Noted