What function can I use to fetch the most recent value of a duplicate (some name)

FILIPKOCHOVSKI

My question is this I am struggling to understand what function I need to use so that it returns the most recent value I have added in the column.

I want to know how to make the VLOOKUP function return the last value I added to the array

as of now it only returns the 1st value it sees(scanning from the top downwards) I want it to return the last value( scan from the bottom upwards)

the stupid example I created

what it returns. It should be novo 4 in for the highlighted line(24234-2342)

jmyzk_cloudsmart_jp

@FILIPKOCHOVSKI @Genevieve P.

My formula was to return the last (newest) item.

If you are looking for the most recent items that fall under codes 24234-2342,353454, etc., use a COLLECT function like Genevieve's to narrow the list.

I have modified the sheet to make it easier to see what COLLECT, COONTIF, and INDEX do in Genevieve's formula, so please take a look if you have time.

• =IF(komentar@row = "NE E DOBRO", INDEX(COLLECT({Column18}, {Column17}, [Primary Column]@row), COUNTIF({Column17}, [Primary Column]@row)))

https://app.smartsheet.com/b/publish?EQBCT=48f0ad40fc7c47c5818e4d78450dd5af

By the way, Macedonia is famous in Japan because we learn about Alexander the Great in world history.😁

jmyzk_cloudsmart_jp

Hi @FILIPKOCHOVSKI

Please try this formula.

• =INDEX({Column18}, COUNT({Column19}))

INDEX() fetches an item from a collection {Column18} based on row indexes COUNT({Column19}.

COUNT({Column19} counts the number of rows of the first Sheet. I used Column19 as the column has data on all rows.

Note: You must set up {Column18} and {Column19} Cross Sheet References.

As you do with VLOOKUP, click Reference Another Sheet and select Column18, and name as Column18.

https://help.smartsheet.com/function/index

BTW, I google translated and found out "NE E DOBRO" means "IT'S NOT GOOD" in Bulgarian.

Happy Yogurt!😁

FILIPKOCHOVSKI

Hello jmyzk_cloudsmart_jp

I tried your formula and something is not right, this is what it returns for all values ->

the reason why I want this to work just like VLOOKUP is that I just want the most recent(newest) edit(Column18) of the product name(Column17) I do not want to waste time searching for an old value just to delete it. I see it as extra work that is not necessary.

Also "NE E DOBRO" means "IT'S NOT GOOD" maybe in Bulgarian too but I am from Macedonia ✌️😊

Genevieve P.

Hey @FILIPKOCHOVSKI

@jmyzk_cloudsmart_jp's idea is actually a pretty good one! But instead of using COUNT to return the number of rows in general, I would use INDEX(COLLECT to narrow down the range to bring back, then COUNTIF for the number of rows to look into, again using the Product Name to narrow down the results.

Try something like this:

=INDEX(COLLECT({Column to Return}, {Product Name Column}, [Product Name]@row), COUNTIF({Product Name Column}, [Product Name]@row))

Cheers,

Genevieve

jmyzk_cloudsmart_jp

@FILIPKOCHOVSKI @Genevieve P.

My formula was to return the last (newest) item.

If you are looking for the most recent items that fall under codes 24234-2342,353454, etc., use a COLLECT function like Genevieve's to narrow the list.

I have modified the sheet to make it easier to see what COLLECT, COONTIF, and INDEX do in Genevieve's formula, so please take a look if you have time.

• =IF(komentar@row = "NE E DOBRO", INDEX(COLLECT({Column18}, {Column17}, [Primary Column]@row), COUNTIF({Column17}, [Primary Column]@row)))

https://app.smartsheet.com/b/publish?EQBCT=48f0ad40fc7c47c5818e4d78450dd5af

By the way, Macedonia is famous in Japan because we learn about Alexander the Great in world history.😁

FILIPKOCHOVSKI

HAHAHAHAH IT WORKS!!

I can't believe it actually works as advertised xD, you two are geniuses in my book.

I am sorry but I consider myself a very bad programmer you two are the best in my book. I was struggling to find a solution for my problem because smartsheet does not have a formula similar to XLOOKUP in Excel 365 where I can specify the search mode.

@jmyzk_cloudsmart_jp , @Genevieve P. if you are ever coming to Skopje, Macedonia make sure you send me a message in this forum I owe you two a beer/coffee witch what you prefer.

Have a great day! ✌️❤️❤️❤️❤️

Genevieve P.

So glad to hear it works for you!

jmyzk_cloudsmart_jp

I just googled Skopje and I must say, the place looks absolutely stunning. Thank you!