Lookup by most recent date
hoping someone can help me, i found a solution on a different thread in this community, however when I apply it to my data, i get skewed results
I'm looking to pull the most recent "score" from one sheet based on the name "bucket" and have the value moved to another sheet in which I am using to dashboard
data sheet to pull from
and where I want it to go
The formula I'm using is =VLOOKUP([Primary Column]@row, {Mock Audits Range 7}, 3)
and the issue is that it is working for some of the buckets but not the 2 circled.
Best Answer
-
Instead of a VLOOKUP formula, I would recommend using an INDEX(COLLECT. This way you can specify in your formula that you want it to pull back the first row matching, so that you have the most recent data (assuming your rows are coming in top-down).
Try something like this:
=INDEX(COLLECT({Score Column}, {Bucket Column}, [Primary Column]@row), 1)
The 1 at the end indicates the 1st match/row.
This also means you're referencing the columns individually instead of in one range, so you can move them around in the source sheet and the formula will continue to work as-is.
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Instead of a VLOOKUP formula, I would recommend using an INDEX(COLLECT. This way you can specify in your formula that you want it to pull back the first row matching, so that you have the most recent data (assuming your rows are coming in top-down).
Try something like this:
=INDEX(COLLECT({Score Column}, {Bucket Column}, [Primary Column]@row), 1)
The 1 at the end indicates the 1st match/row.
This also means you're referencing the columns individually instead of in one range, so you can move them around in the source sheet and the formula will continue to work as-is.
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!