Last value from INDEX MATCH

narora

I am a smartsheets newbie, but have good experience with Excel and have been trying some things for an inventory management tool that I am creating.

Typically in excel I use the following formula to pick the last value from INDEX MATCH and it works well to give me the last matching value in the range of C3 to C12 from the match between E3 and the range of repeated values in B3 to B12.

=INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3)))

Somehow it seems that a similar formula in smartsheets throws a #INVALID OPERATION error.

=INDEX({LoggerData Range 4}, MATCH(2, 1 / ({LoggerData Range 2} = [Barcode #]@row)))

Am I missing something, or is this something I can do to make this work?

Andrée Starå

Hi @narora

I hope you're well and safe!

Try something like this.

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

narora

Thanks @Andrée Starå for you quick response.

As you mentioned, the formula works well for me in the United States with the comma, but I do not want the first match of "ColumnWithTheValueToMatchAgainsTheCell" rather I want the last match. If I could explain by example:

AA 40

BB 50

CC 30

BB 100

DD 100

BB 70

EE 60

GG 80

VV 40

BB 10

Today INDEX MATCH of BB the following formula works well to give me 50, but I want to see if smartsheets can provide me 10.

=INDEX({LoggerData Range 4}, MATCH([Barcode #]@row, {LoggerData Range 2}, 0))

If this data was in excel the following formula does give me 10 as an answer =INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3)))

but in smartsheets it gives me #INVALID OPERATION