+ Reply to Thread
Results 1 to 7 of 7

Using ISNUMBER SEARCH with INDEX MATCH

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Cape Town, South Africa
    MS-Off Ver
    2013
    Posts
    9

    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.

    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 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.
    Last edited by Emile du Toit; 03-21-2017 at 05:56 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,981

    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. #3
    Registered User
    Join Date
    08-16-2016
    Location
    Cape Town, South Africa
    MS-Off Ver
    2013
    Posts
    9

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,981

    Re: Using ISNUMBER SEARCH with INDEX MATCH

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

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    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

  5. #5
    Registered User
    Join Date
    08-16-2016
    Location
    Cape Town, South Africa
    MS-Off Ver
    2013
    Posts
    9

    Re: Using ISNUMBER SEARCH with INDEX MATCH

    Thanks Pete

  6. #6
    Registered User
    Join Date
    06-15-2020
    Location
    Tabriz, Iran
    MS-Off Ver
    97/2000/03/07/13/2016
    Posts
    2

    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,796

    Re: Using ISNUMBER SEARCH with INDEX MATCH

    Quote Originally Posted by MOHsab View Post
    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!
    Administrative Note:

    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.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)
    By Emile du Toit in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2016, 07:29 AM
  2. Index Match with nested isnumber
    By JamesArmitage in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-22-2016, 08:04 AM
  3. Match, Search or Index? :S
    By denby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2014, 12:52 PM
  4. [SOLVED] Working INDEX MATCH with SEARCH, but I need to add another MATCH to the formula!
    By DaveBre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2014, 01:03 AM
  5. need help using index/match to search grid
    By tsiguy96 in forum Excel General
    Replies: 2
    Last Post: 12-11-2013, 08:33 AM
  6. USING IF ISTEXT OR ISNUMBER THEN INDEX MATCH OR calculation
    By pippib in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2012, 01:26 AM
  7. Search,Index,Match which one
    By rlkerr1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2007, 04:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1