Vlookup issue again

nature969

Board Regular
Joined
Dec 20, 2016
Messages
55
Hi all

I am stacking with this vlookup. what I want to do as below

if supplier name (in column A) says ABC ltd take part number in column B for lookup. if supplier name isn't ABC ltd use part number in column C for lookup.

so column E & F is my data source for look up.

Can anyone help me with a formula ?
Thanks
1596165044518.png
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Just to get the ball rolling
20-07-30.xlsx
ABCDEF
1supplier namePart number 1Part number 2Data in other sheet
2ABC Ltd01A1B$0.50Part number$
3102A2B$3.5001A$0.50
42503A3B#N/A02A$5.00
5204A4B$4.5003A$0.50
6ABC Ltd05A5B$1.507B$6.00
75906A6B#N/A05A$1.50
8ABC Ltd07A7B$2.50Da $2.00
907A$2.50
10JP05$9.00
112B$3.50
12A1$4.00
134B$4.50
Look Up Data
Cell Formulas
RangeFormula
D2:D8D2=VLOOKUP(IF(A2="ABC Ltd",B2,C2),$E$3:$F$13,2,0)
 
Upvote 0
Just to get the ball rolling
20-07-30.xlsx
ABCDEF
1supplier namePart number 1Part number 2Data in other sheet
2ABC Ltd01A1B$0.50Part number$
3102A2B$3.5001A$0.50
42503A3B#N/A02A$5.00
5204A4B$4.5003A$0.50
6ABC Ltd05A5B$1.507B$6.00
75906A6B#N/A05A$1.50
8ABC Ltd07A7B$2.50Da $2.00
907A$2.50
10JP05$9.00
112B$3.50
12A1$4.00
134B$4.50
Look Up Data
Cell Formulas
RangeFormula
D2:D8D2=VLOOKUP(IF(A2="ABC Ltd",B2,C2),$E$3:$F$13,2,0)
Thanks Ezguy4u :)

Now I need 2nd part to add in the formula "if supplier name isn't ABC ltd use part number in column C for lookup" Can someone help?
 
Upvote 0
Now I need 2nd part to add in the formula "if supplier name isn't ABC ltd use part number in column C for lookup" Can someone help?
 
Upvote 0
Unless i misunderstand the question @Ezguy4u has the answer in post #2 above

another way is
=IF(A2="ABC Ltd",VLOOKUP(B2,$E$3:$F$13,2,FALSE),VLOOKUP(C2,$E$3:$F$13,2,FALSE))
 
Upvote 0
Unless i misunderstand the question @Ezguy4u has the answer in post #2 above

another way is
=IF(A2="ABC Ltd",VLOOKUP(B2,$E$3:$F$13,2,FALSE),VLOOKUP(C2,$E$3:$F$13,2,FALSE))
Thanks Dave87

Your formula only give result for 2nd condition ( if supplier name isn't ABC ltd use part number in column C for lookup). The 1st condition didn't calculate.
 
Upvote 0
Thanks Dave87

Your formula only give result for 2nd condition ( if supplier name isn't ABC ltd use part number in column C for lookup). The 1st condition didn't calculate.

that's strange as it works fine on my document (see screenshot)

i've looked over my answer and the answer in Post #2 above and both works (unless i'm misunderstanding what you're looking for)
 

Attachments

  • Formula is OK.png
    Formula is OK.png
    21.7 KB · Views: 4
Upvote 0
that's strange as it works fine on my document (see screenshot)

i've looked over my answer and the answer in Post #2 above and both works (unless i'm misunderstanding what you're looking for)
Hi Dave87

I have tried again if I use the formula in example data ( above) it's work fine. but if I apply it to my real data. the formula seems doesn't work.

See below
Don't know where went wrong.

1596411948687.png
 

Attachments

  • 1596411901888.png
    1596411901888.png
    22.3 KB · Views: 2
Upvote 0
try converting your numbers to numbers, on the little green triangle in column aa above 700000394 & convert to number and let me know if this works
 
Upvote 0
try converting your numbers to numbers, on the little green triangle in column aa above 700000394 & convert to number and let me know if this works

No it doesn't work.

actually in column K & AA I have formula two. Does it make any different? I tried to manually type the data in column AA and didn't work
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top