Using ISNUMBER SEARCH with INDEX MATCH

Emile

New member
Joined
Jun 30, 2016
Messages
3
Reaction score
0
Points
0
Old but thorough product data
OLDSKUTitleDescription
ProductXXXMr XX description detailed
ProductYYYMr YY description detailed
ProductZZZMr ZZ description detailed


New but sparse product data
NEWDescriptionWeightSKU
ProductA Description simple20AAA
ProductZ description simple15ZZZ
ProductB description simple10BBB


Combining best new and old data
COMBINEDSKUTITLEDESCRIPTION
ProductAAAA description simple
ProductZZZ
ProductBBB


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!!

NB:I have also published this on Mr Excel and Excel Formula but have yet to receive a solution. Will close all as soon as I do.
 


NB:I have also published this on Mr Excel and Excel Formula but have yet to receive a solution. Will close all as soon as I do.

Hi
Thank you for warning about the cross posts

Could you be so kind as to post the two links ( as text, not having 5 posts yet adding real links won't work) ?
Thank you
 
Yes I tried that but it keeps seeing it as a link. excelforum.com/excel-formulas-and-functions/1178440-using-isnumber-search-with-index-match.html and mrexcel.com/forum/excel-questions/996751-using-isnumber-search-index-match.html#post4783730
 
Did you try?

=IF(ISNUMBER(MATCH('NEW'!$D2,'OLD'!$B$2:$B$4,0)),INDEX('OLD'!$D$2:$D$4,MATCH('NEW'!$D2,'OLD'!$B$2:$B$4,0)),'NEW'!B2)
 
Back
Top