Lookup by most recent date

Scott Johnson

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.

Genevieve P.

Hi @Scott Johnson

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

Genevieve P.

Hi @Scott Johnson

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