Lookup within a Character String...?

[PM1]

Right, this is a puzzle that I have been trying to get my head around all week and I've gotten to the point that my head is splitting.

Scenario:- 2 Spreadsheets, No 1 records all Purchases:

Purch Orders BP²I.xls
	A	B	C	D	E	F
1	PSPONo	Details	Qty	UnitCost(GBP)	Expense/Asset	AssetValue
2	80	RackServer1:pSeries650Model6M2CEC	1	2,753.25	9	24,929
3	80	I/ODrawer3:ModelD20I/ODrawer	1	2,637.00	9	5,181
4	80	I/ODrawer2:ModelD20I/ODrawer	2	2,637.00	9	8,981
5	80	7014-T42:Rack1:IBMRS/6000RackModelT42	1	1,798.50	9	2,689
6	80	IBM7316-TF3Rack-MountedFlatPanelConsoleKit	1	1,605.50	9	2,059
7	80	ProLiantDL580G2R02XeonMP2.7GHz-2MB,2P,2GB	3	5,528.00	9	7,952
8	80	ProLiantDL380(G3)Xeon3.2GHz-1MB,1GB	11	1,331.00	9	3,859
9	80	SunFireV480Server	3	12,464.00	9	24,345
Sheet1

No contains the Asset No;s:

Purch Orders BP²I.xls
	H	I	J	K
1	AssetCode	PSPONo	AssetTypeName	AssetValue
2	LN065229	80	HPProliantDL580G2R02	24,929
3	LN065231	80	HPProliantDL580G2R02	24,929
4	LN065232	80	SunFireV480	24,929
5	LN065233	80	SunFireV480	24,929
6	LN065234	80	SunFireV440	24,929
7	LN065235	80	SunFireV480	24,929
8	LN065236	80	HPDL380(G3)	24,929
9	LN065237	80	HPDL380(G3)	24,929
10	LN065238	80	HPDL380(G3)	24,929
11	LN065239	80	HPDL380(G3)	24,929
12	LN065240	80	HPDL380(G3)	24,929
13	LN065241	80	HPDL380(G3)	24,929
14	LN065242	80	HPDL380(G3)	24,929
15	LN065243	80	HPDL380(G3)	24,929
16	LN065244	80	HPDL380(G3)	24,929
17	LN065245	80	HPDL380(G3)	24,929
18	LN065320	80	SunFireV440	24,929
Sheet1

When I look for the PO No from Sheet 2 in Sheet 1 it finds the first instance and records the value stored in Col "F". However I need it to look for a character string in Sheet 2 Col "J" (DL380) and search for that in Sheet 1 Col "B" and then return the correct value, which in this case should be £3859

Any ideas.?

 

[barry houdini]

I'm not quite sure I understand, do you want to search for the PO number in column A AND "DL380" in column B or is it just the latter?

How do you determine which part of your entries in column J you want to search for?

In the case of J8 "DL380" is the middle "word" of 3, i.e. it is preceded by the first space and followed by the second but for which part of J7 do you want to search? I note, for instance, that J7 is "SunFire V480" but in sheet 1 Sun and Fire are separated by a space. This may make it difficult to get a match without some sort of "fuzzy matching"

 

[PM1]

Barry, I know it's confusing, I have to try and translate it

You are correct in what you say, is there a way to just look for the numeric digits in the field (ie 380, 44, 580, etc) and use that along with the PO No in Col "A" . So in sheet 2 it would look for PO No 80 + 580 in sheet 1