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

luisa anjos

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

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.

luisa anjos

@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?

Thanks,

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

@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

@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.