Using ISNUMBER SEARCH with INDEX MATCH

[Emile du Toit]

Old but thorough product data
[TABLE="width: 500"]
<tbody>[TR]
[TD]OLD[/TD]
[TD]SKU[/TD]
[TD]Title[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]Product [/TD]
[TD]XXX[/TD]
[TD]Mr X[/TD]
[TD]X description detailed[/TD]
[/TR]
[TR]
[TD]Product [/TD]
[TD]YYY[/TD]
[TD]Mr Y[/TD]
[TD]Y Description detailed[/TD]
[/TR]
[TR]
[TD]Product [/TD]
[TD]ZZZ[/TD]
[TD]Mr Z[/TD]
[TD]Z description detailed[/TD]
[/TR]
</tbody>[/TABLE]

New but sparse product data
[TABLE="width: 500"]
<tbody>[TR]
[TD]NEW[/TD]
[TD]Description[/TD]
[TD]Weight[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]A description simple[/TD]
[TD]20[/TD]
[TD]AAA[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Z description simple[/TD]
[TD]15[/TD]
[TD]ZZZ[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]B description simple[/TD]
[TD]10[/TD]
[TD]BBB[/TD]
[/TR]
</tbody>[/TABLE]

Combining best new and old data
[TABLE="width: 500"]
<tbody>[TR]
[TD]COMBINED[/TD]
[TD]SKU[/TD]
[TD]Title[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD]A description simple[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD]Z description detailed[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[TD]B description simple[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to combine data from a detailed old product spreadsheet with much sparser data that is available for new products. Where the old data is available (here we are just going to look at the Description column) I want to add this data into the cell. Where a product is new though - and so it is not already detailed on my old spreadsheet - I want to add the new spreadsheet Description (as this is all there is). As you can see although the OLD and COMBINED spreadsheets have the same column order, the NEW one does not.

So in the third spreadsheet I am working in the Description column to populate the descriptions. In the first cell (intersection of the first product row and Description column) I am comparing the first SKU (product identifier) of the NEW spreadsheet with the entire SKU column of the OLD spreadsheet, to see if the SKU already exists in the OLD spreadsheet. If it does match then I want the cell to include the OLD spreadsheet Description data from the product with the SKU that matches the NEW product. If the NEW spreadsheet SKU does not match the OLD spreadsheet SKU (ie it is a genuinely new product) then I want to populate the cell with the NEW spreadsheet Description data. You can see I have added in the correct data for the COMBINED spreadsheet Description column.

The formula I tried (to populate D2) was:
=IF(ISNUMBER(SEARCH('NEW'!$D2,'OLD'!$B$2:$B$4)),INDEX('OLD'!$D$2:$D$4,MATCH('NEW'!$D2,'OLD'!$B$2:$B$4,0)),'NEW'!B2)

However, there must be an error as it returns the NEW descriptions whether or not the SKU's match.

Please help!!

 

[63falcondude]

How about something like this?

Excel 2010
	A	B	C	D
1	OLD	SKU	Title	Description
2	Product	XXX	Mr X	X description detailed
3	Product	YYY	Mr Y	Y Description detailed
4	Product	ZZZ	Mr Z	Z description detailed
OLD

Excel 2010
	A	B	C	D
1	NEW	Description	Weight	SKU
2	Product	A description simple	20	AAA
3	Product	Z description simple	15	ZZZ
4	Product	B description simple	10	BBB
NEW

Excel 2010
	A	B	C	D
1	COMBINED	SKU	Title	Description
2	Product	AAA		A description simple
3	Product	ZZZ		Z description detailed
4	Product	BBB		B description simple
Combined

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX(OLD!D:D,MATCH(B2,OLD!B:B,0)),INDEX(NEW!B:B,MATCH(B2,NEW!D:D,0)))

 

[Emile du Toit]

I think maybe you have an error - it is showing #N/A. Possibly the 'B2' you have needs to assigned to either the NEW or OLD spreadsheet?

 

[63falcondude]

I think maybe you have an error - it is showing #N/A. Possibly the 'B2' you have needs to assigned to either the NEW or OLD spreadsheet?

As you can see from my example in post #2, the formula returns "A description simple" in D2 and not "#N/A".

B2 is in the "Combined" sheet (as is the formula) so it doesn't need a sheet reference since it is referring to B2 of the "Combined" sheet.

You need something unique in the "Combined" sheet to match against the "OLD" and "NEW" sheets so I added SKU's into the "Combined" sheet assuming that you simply left those out in your example.

 

[Emile du Toit]

Thanks. Yes i can get it to provide SKU's for combined so that isn't a problem. However, I have used your exact equation and all three results are the 'simple' descriptions taken from the NEW sheet. This appears to be a similar problem to what I had with my equation (only taking data from the NEW sheet).

 

[Emile du Toit]

SOLVED. Thanks guys, this post has been solved here: https://www.excelforum.com/excel-fo...mber-search-with-index-match.html#post4610266
I am not too sure how to make this more clear so people don't waste their time re-solving it?