Index Match and Filter?

Hello!

I've successfully pulled data from one sheet to another via Index/Match while using an 'IF' statement within the same sheet to filter results using the following formula.

=IFERROR(IF([Budget Action Requested]@row = "Initial", (INDEX({Date Specs Completed}, MATCH([Unique ID]@row, {Unique ID}, 0))), "N/A"), "")

But now I'm interested in using the Index/Match while filtering the other sheet for resultant data. Is this possible?

Here is my formula - which returns nothing, where it should return a very specific date.

=IFERROR(IF({Action Requested}, ="Initial", (INDEX({Budget Sent}, MATCH([Unique ID]@row, {Unique ID}, 0)))), " ")

I'm relatively new at this, so let me know if my question needs re-phrasing. Thank you!

Meredith

Tags:

Answers

  • Davidp
    Davidp ✭✭
    edited 03/08/23

    I recently had a similar if not same question that with the assistance of community experts my inquiry was solved.

    your if error is closed off as showing a null or blank value by the “ “ at the end. This is your result if your index/match results an error. To test this suspicion, add some text to your blank quotes and see what happens.


    in my situation I was looking to search two separate similar sheets to look a match that is expected in one or the other.

    im pulling this from recollection as I don’t have the successful function text on hand. I did get creative with mine so it was a long one full of nested functions.


    =if(isblank({employee name}@row, “ “, iferror(index(sheet2_id column, match({employee name}@row, sheet2_name column, 0)), then I ran a second index/match function on a separate sheet for the false portion of the first index/match resulted an error.



    for yours, try this.

    =if({action requested} “=initial”, iferror(INDEX({Budget Sent}, MATCH([Unique ID]@row, {Unique ID}, 0)))), " ")


    this tests your action requesTed column, if it is marked as initial then the if error function starts by running the index/match function. If a matching unique id is found it will give you that number, if no match or error, then your result will not show anything due to the “ “ declaration at the end. Hope this works, it’ll be my first successful solve if so!

    keep me posted, I hope I’m able to help you solve this.

  • Thanks David,

    You've given me a few things to think about. I've tried to move the IF statement outside the IFERROR statement, and tried to remove the IFERROR statement altogether.

    It's not quite working yet.

    =IF({Action Requested} = "Initial", (INDEX({Budget Sent}, MATCH([Unique ID]@row, {Unique ID}, 0))), "") is an 'INVALID OPERATION'

    Doesn't anything look inherently wrong with this formula?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!