+ Reply to Thread
Results 1 to 12 of 12

IF Statement With SEARCH and MATCH?

  1. #1
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    IF Statement With SEARCH and MATCH?

    This may be a tough one for me to explain but I've posted a screen shot and attached example spreadsheet. Basically, if a cell has the word "RECONCILED" in it, I want to look at the cell above it and find see if the word "CHANGE" is there. If it is, it needs to look at the cell above that and see if the word "CHANGE" is there. It will repeat this process until the last "CHANGE" is located and choose the value in a cell adjacent to it. Another formula would do the same thing except look for the first "CHANGE" instead of the last.

    Hope all that makes sense... lol. This is kind of a tricky one.

    dateexample.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF Statement With SEARCH and MATCH?

    Hi,

    In C10 use:

    Please Login or Register  to view this content.
    In D10:
    Please Login or Register  to view this content.
    **D10 is an array formula meaning that in oder to work correctly, it needs to be entered with control + shift + enter. When you enter a formula this way, you'll see the formula wrapped in curly braces {} in the formula bar.
    Attached Files Attached Files

  3. #3
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: IF Statement With SEARCH and MATCH?

    This is close! But not quite... I may should have told you that the formula has to run down the entire column for each, which means, I will need an additional 2 helper columns. See if the attached explains better.
    Attached Files Attached Files
    Last edited by AliGW; 06-12-2020 at 09:44 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF Statement With SEARCH and MATCH?

    Hi
    You dont have to quote me....

    please see attached the revised file.

    E2 and down:

    Please Login or Register  to view this content.
    F2 and down:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF Statement With SEARCH and MATCH?

    I also assumed (and you didnt mention as well) that when "CHANGE" appears in column B - the output should be C or D respectively.

    If it's wrong - please provide more explicit instructions....

  6. #6
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: IF Statement With SEARCH and MATCH?

    Still close but not quite... and sorry for the lack of explanation. It's not intentional. I just tend to leave out details sometimes. It's a fault of mine that I have been trying to correct but alas... here we are.

    Attachment 682234
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: IF Statement With SEARCH and MATCH?

    Not intentional, but you know you do it ... For the sake of those trying to help you, please take a few more minutes before posting to check that your query or response covers all bases - more haste, less speed, as we are seeing here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: IF Statement With SEARCH and MATCH?

    Quote Originally Posted by AliGW View Post
    Not intentional, but you know you do it ...
    Thanks for the tip, Ali! I'll certainly take your advice.

    I think my last example was much better.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF Statement With SEARCH and MATCH?

    I dont understand
    when you say: " find the last cell showing "CHANGE" above that" do you mean the farthest or the closest cell?
    In E10 you wanted it to be the farthest above B9 which would be B6 and thus returned the value in C6.

    But then in E21 you say that the result should be 2020-06-04, which is C20?? According to your logic it should be 2020-06-03 (cell C9).

    Please explain and add the cell numbers you expect to be returned since the dates are not unique and confusing.

    Thanks.

  10. #10
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: IF Statement With SEARCH and MATCH?

    Quote Originally Posted by belinda200 View Post
    I dont understand... Please explain and add the cell numbers you expect to be returned since the dates are not unique and confusing...
    Your message made me realize that a unique identifier was needed rather than just the dates. Once I added a helper column for that, I was able to adjust your formula suggestion to make it work for me.

    Thanks for the help.

  11. #11
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF Statement With SEARCH and MATCH?

    You're welcome , let me know what was your solution eventually.

  12. #12
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: IF Statement With SEARCH and MATCH?

    Quote Originally Posted by belinda200 View Post
    You're welcome , let me know what was your solution eventually.
    My apologies but I have not been on in a while. I've probably done rearranged the data for that formula to where it isn't quite the same as before so the solution wouldn't match what we were discussing above.

+ 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. Replies: 6
    Last Post: 05-22-2019, 11:29 PM
  2. Replies: 5
    Last Post: 03-29-2018, 01:56 PM
  3. Replies: 1
    Last Post: 10-06-2017, 05:02 PM
  4. Help joining an if statement with a search statement
    By Segurde in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2014, 03:13 PM
  5. Replies: 0
    Last Post: 09-21-2013, 09:03 PM
  6. [SOLVED] Match search terms, and add data in next column. Multi search problem.
    By Wales MB in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-23-2013, 06:10 PM
  7. Search a worbook, match cells, input stuff into first workbook if they match.
    By EvilErniesSK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2012, 11:49 AM

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