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
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Answers
-
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?
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!