VLOOKUP/ INDEX : Is that a way to performa reverse search starting at the last item?

luisa anjos
luisa anjos ✭✭
edited 01/10/23 in Formulas and Functions
Hi all,

I need to make a formula to pull data from another worksheet, based on a math, but that does this search from bottom to top.

That is, I have a spreadsheet of audits that are carried out, and in each one we put the identification number of the tool used. I need to make another spreadsheet that shows the last audit performed for each tool.

When using vlookup or index/math it brings me the first audit, not the last.

What I need is that excel XLOOKUP function, "search mode". Is there a way to do this in the smartsheet?


  • sharkasits
    sharkasits ✭✭✭✭✭

    You can use the Index(Collect(<Your normal Collect Statement>), Count(Collect(<Same Collect Statement>)) to get the last instance.

    When you normally do an index collect, you use Index(collect(),1) to get the first instance. Replacing the 1 with the Count(collect()), gives you the last instance. I use this a lot to pull the current version of something from a helper sheet, or count(collect())-1 to pull the previous version of something. Let me know if this doesn't make sense, sometimes it's hard to type out without using the actual formulas.

  • @sharkasits Thanks for the help!!

    however I am seeing an inconsistency in this formula Count(collect()).

    I`ve used the following formula

    =COUNT((COLLECT({BR PG&E AUDIT 2022 - sid}, {BR PG&E AUDIT 2022 - PQUBE}, SN@row)))

    An example, it brings the number 3 to SN P3005915

    and it was supposed to bring 7, as shown in the images below

    Do you have any idea what it could be?


  • sharkasits
    sharkasits ✭✭✭✭✭

    @luisa anjos That's strange... I haven't seen that before, I was going to say it looks like it's adding a DISTINCT in there, but even that would be 4 not 3. Is that the full formula, or is there anything else in it?

    I would try a few things to troubleshoot:

    1. remove the extra parenthesis "=COUNT(COLLECT({BR PG&E AUDIT 2022 - sid}, {BR PG&E AUDIT 2022 - PQUBE}, SN@row))"
    2. check that your references are referring to the full column in the data sheet
    3. check that you don't have spaces after some of the PQUBE numbers in the data sheet.

  • luisa anjos
    luisa anjos ✭✭
    edited 01/11/23

    @sharkasits yes, this is all the formula.

    I checked all 3 items.

    1 - ok

    2 - ok

    3 - there are some lines that PQUBE column are blank. Because not all SID need to have a PQUBE number. I fill all those lines but didn't resolve. But SID continues with some blank lines.

  • sharkasits
    sharkasits ✭✭✭✭✭

    @luisa anjos the blank lines shouldn't affect it. If there are spaces after the number in the cell it might affect it though.

    Maybe try going the opposite way.. in your data sheet add a test column with ...

    index(collect(<a unique column in your summary sheet>, {data sheet - SN}, [PBQE Serial Number]@row),1)

    and see which ones aren't mapping correctly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!