# Using ISNUMBER SEARCH with INDEX MATCH

1. ## Using ISNUMBER SEARCH with INDEX MATCH

Old but thorough product data
OLD.png

New but sparse product data
NEW.png

Combining best new and old data
COMBINED.png

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.

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.

NB: I have also asked for help with this problem at https://www.mrexcel.com/forum/excel-...ml#post4783039 and Excel Guru but unfortunately havn't received a workable solution yet.

2. ## Re: Using ISNUMBER SEARCH with INDEX MATCH

Try it like this:

=IFERROR(INDEX('OLD'!\$D\$2:\$D\$4,MATCH('NEW'!\$D2,'OLD'!\$B\$2:\$B\$4,0)),'NEW'!B2)

then copy down.

Hope this helps.

Pete

3. ## Re: Using ISNUMBER SEARCH with INDEX MATCH

Thank you Pete that us brilliant!

I just want to make sure I understand the logic. I know this sounds clumsy, but basically you are saying the following:
If you cannot match NEW!\$D2 with any cells in OLD!\$B\$2:\$B\$4, then use NEW!B2, otherwise use the cell on OLD!\$D\$2:\$D\$4 that is in the same row where NEW!\$D2 matched with OLD!\$B\$2:\$B\$4

Is this correct?
Thank again for the solution!

4. ## Re: Using ISNUMBER SEARCH with INDEX MATCH

Originally Posted by Emile du Toit
... Is this correct?...
Yes, spot on.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks Pete

6. ## Re: Using ISNUMBER SEARCH with INDEX MATCH

I have a serious question, why I couldn't get the result from this formula even the find_text is already in the withing_text?
ISNUMBER(SEARCH(A1,INDEX(range1,MATCH(1,INDEX(b2=range2)*(c2=range3),0,1),0))))

I really need it to know, please!

7. ## Re: Using ISNUMBER SEARCH with INDEX MATCH

Originally Posted by MOHsab
I have a serious question, why I couldn't get the result from this formula even the find_text is already in the withing_text?
ISNUMBER(SEARCH(A1,INDEX(range1,MATCH(1,INDEX(b2=range2)*(c2=range3),0,1),0))))

I really need it to know, please!

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)