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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!