Vlookup issue again

[nature969]

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

 

[Dave87]

a couple of questions and suggestions

1. For the numbers in column K, AA, and sheet Other Catalogue column A; are they whole numbers or is there decimal places?
2. use =isnumber(AA3) to see if the lookup value is a number, then do the same formula for 70000359 in Other Catalogue column A
3. find 700000359 in column A on sheet Other Catalogue, copy and paste values this cell into AA3 and see what happens

 

[nature969]

a couple of questions and suggestions

1. For the numbers in column K, AA, and sheet Other Catalogue column A; are they whole numbers or is there decimal places?
2. use =isnumber(AA3) to see if the lookup value is a number, then do the same formula for 70000359 in Other Catalogue column A
3. find 700000359 in column A on sheet Other Catalogue, copy and paste values this cell into AA3 and see what happens

I have tried your suggestions:
1, column K ,AA & sheet other catalogue column A are all number, but some number have "." for example 123.54A or 123.659
2, I used isnumber for K , AA data , all return as false ( I guess I have formulas in both columns).
3, I also tried this before- copy and paste as values and it doesn't work.