MAX and INDEX
I need help with a formula.
I am trying to create an index formula to get back a specific column data but i want to get the latest one.
This is my sheet im referencing. the first column (Plan of Care Cognito ROw ID) is my auto numbering column.
On my original sheet im creating the formula on, i have a "HHAX Admission ID" column. i want to find the row with the same "HHAX Admission ID" and return the "Plan of Care PDF" data. but sometimes the same "HHAX Admission ID" would be on there 2 times so i want to return the latest one based on the auto numbering column
Currently im using a VLOOKUP function of =VLOOKUP([HHAX AdmissionID]@row, {Plan of Care sheet}, 9, false) but i need to change it for these 2 reasons
- it doesnt bring back the latest entry
- i'd like to change it to an INDEX formula so i can move the rows around and not have to fix the lookup number column
Thank you in advance
Best Answer
-
Try this:
=INDEX({PDF Column}, MATCH(MAX(COLLECT({Row ID}, {Admission ID}, @cell = [HHAX AdmissionID]@row)), {Admission ID}, 0))
Answers
-
Try this:
=INDEX({PDF Column}, MATCH(MAX(COLLECT({Row ID}, {Admission ID}, @cell = [HHAX AdmissionID]@row)), {Admission ID}, 0))
-
Thank you for your response. i tried that, with my references but i got a #NO MATCH
heres the formula with the correct references i created:
=INDEX({Plan of Care PDF}, MATCH(MAX(COLLECT({Plan of Care ID}, {Plan of Care Admission ID}, @cell = [Admission ID]@row)), {Plan of Care Admission ID}, 0))
when i do the formula of =MAX(COLLECT({Plan of Care ID}, {Plan of Care Admission ID}, @cell = [Admission ID]@row)), im getting the row id number which shows me its finding the right row but its something in the index/match portion of the formula which it isnt finding
-
That's my fault. Sorry about that. The second {Plan of Care Admission ID} should actually be {Plan of Care ID}.
-
this worked! thank you so much for your help
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!