Complicated Lookup

[Switch]

Hi all,

Can anyone please help!!

In column A and B, are examples of descriptions that come through from a sales system to Excel.

In Column C is an example of what I would like to change the descriptions to in columns A and B respectively.

What the formula needs to do is match the 5 digit number at the beginning of column C to the same 5 digits found somewhere in A or B and then return a description that is the same as column C (the lookup table)

I would greatly appreciate somke help.

CUBE CODES PRACTITIONER.xls
	A	B	C	D
1	Eaxmple1ofalistasitcomesthroughfromMasterSalesSystem	Eaxmple2ofalistasitcomesthroughfromMasterSalesSystem	MasterDescriptionList
2	9303002-CELLIODCHART	CelloidChart_03002	03002_CelloidChart
3	9303023-CELLOIDSP96(84)	SP96(84)_03023	03023_SP96(84)
4	9303024-CELLOIDSS69(84)	SS69(84)_03024	03024_SS69(84)
5	9303025-CELLOIDCP57(84)	CP57(84)_03025	03025_CP57(84)
6	9303026-CELLOIDIP82(84)	IP82(84)_03026	03026_IP82(84)
7	9303027-CELLOIDPP85(84)	PP85(84)_03027	03027_PP85(84)
Sheet2

Kind regards
Switch

 

[brucesw]

Switch,
Hope I don't tread on the regular's toes, but try a combination of the FIND, LEFT, RIGHT, MID, PROPER and TRIM functions.

For Column b: =RIGHT(B2,LEN(B2)-FIND("_",B2))&"_"&LEFT(B2,FIND("_",B2)-1)
For column a: =MID(A2,3,FIND("-",A2)-4)&"_"&TRIM(PROPER(RIGHT(A2,LEN(A2)-FIND("-",A2)-1)))

Good luck

 

[BigC]

From the example data you given, it looks like the data in column A may be redundant, as you can get the syntax/format like C from the data in B.

However...(hoping I've understood the requirement properly)
Add a column to the left of column A (for this explanantion call it New) into which you enter the following formula in each cell corresponding to data in A
=MID(A1,3,5)
=>> creates a lookup/key column by returning the 5 digit code required for the lookup.

Assume the 5 digit item code for the selected item is input into E1, in the cell where you want the data returned (say F1), enter:
=VLOOKUP(E1,New1:New999,4,false)
returns the single value in column C (4th column from New column) for the matching value in New column.

HTH