VLOOKUP partial match not playing the game

[KiwiRickToo]

Hi All

I'm trying to get VLOOKUP to work using a partial match on a cell that contains two values seperated by a / eg. 12345/54321 but the results are not correct. The VLOOKUP function works if I enter a value from a cell that has just a single number but not from cell that has the two numbers.

Attached spreadsheet shows the issue

In the example on Sheet Test Data I need to be able to get VLOOKUP to work on Row 18

Any help appreciated thanks

[AliGW]

You are not likely to be able to get this to work simply with VLOOKUP: your formula needs the entire contents of the lookup cell to match the lookup value.

An approximate match (TRUE) is not really appropriate either, since your codes are surely unique values?

[KiwiRickToo]

You are not likely to be able to get this to work simply with VLOOKUP: your formula needs the entire contents of the lookup cell to match the lookup value

I need to be able to extrapolate values using just one of the two numbers e.g. 12345 or 54321 not 12345/54321. In my real data 12345 is a unique Work Order Number and so is 54321. They are combined in the cell for another reason. Is there another formula that I can use?

[AliGW]

It's not going to be easy unless you separate out these numbers. Why do they have to be together?

[Pete_UK]

You could try it this way in C2:

=IF(COUNTIF(SalesOrders!$B:$B,$B2),VLOOKUP($B2,SalesOrders!$B:$G,2,0),VLOOKUP("*"&$B2&"*",SalesOrders!$B:$G,2,0))

then copy across and down (making appropriate changes to the 2 in red for the other columns).

Hope this helps.

Pete

[bebo021999]

Another way:
In C2:

Please Login or Register  to view this content.

Copy to D2:E2

In E2, change E$2:E$19 to G$2:G$19

[kvsrinivasamurthy]

ARRAY formula In Row 2, then drag down.

Please Login or Register  to view this content.

Pl see file.

[KiwiRickToo]

Thank you everybody. All three examples work. Although I must say they look a bit daunting