Sheet Summary - grabbing Text of the largest entry using COLLECT

artificiellexx

Hello all,

I need to be able to search through a list of IDs and grab text from other column(s) associated with the highest entry for the ID.

My ID is made up of two numbers: the first represents the room, and the second is the next sequential number. An example would be 300.5, where it would be Room 300 and the 5th (& highest) entry. I have three columns: the Room is listed separately, the full ID, and then a column with text. I want to create a formula that will scan through the "Room" column to find "300", then search in the full ID column to find the highest number (as there would be 300.1, 300.2, 300.3, ...), and then return the text in the text column. I've done this same process using MAX & Collect to report back dates/values, but I can't figure out the text version of this as MAX only returns numbers or dates. (My formula for that was =MAX(COLLECT([Column with a number]:[Column with a number], Room:Room, "118")) )

I've tried: =INDEX([Text Column]:[Text Column], COLLECT([Text Column]:[Text Column], Room:Room, "300"), 4) which returns a Invalid Data Type error. I'm using "4" as that is the position of the Text Column. Would love some help on this!

Thank you!

Paul Newcome

Try this...

=INDEX(COLLECT([Text Column]:[Text Column], Room:Room, @cell = 300, [Full ID]:[Full ID], @cell = MAX(COLLECT([Full ID]:[FUll ID], Room:Room, @cell = 300))), 1)