Lookup by most recent date

Options

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 Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!