problem with offset to find the nearest string

Using your posted workbook...
I'm guessing that there are more parameters, but for what you've described, try this formula.

Please Login or Register  to view this content.

Is that something you can work with?

@ Ron Coderre:

Am I correct in thinking that if there were more than two entries that start with "m/s" that the "lookup(2, would have to be increased to something larger than the possible number of occurrences of "m/s" in order to get the last entry?

Actually, no...
In the formula I posted: =IFERROR(LOOKUP(2,1/(LEFT($B$2:$B$13,3)="m/s"),$B$2:$B$13),"no match")

This section: 1/(LEFT($B$2:$B$13,3)="m/s")
returns an array of 1's (for matched items) and #DIV/0!'s (for non-matching items)

Since the LOOKUP is searching for a 2...which is larger than any of the array values (which have a max of 1)...it will match on the last numeric data.
That last numeric value will be the last matched item.

Experiment with it.

Does that help?

@ Ron Coderre:

Thanks for the explanation. I did a bit of experimenting, adding additional "m/s" values in the column and highlighting the section in the formula and hitting F9 to show the result of that part of the formula, could very well see what you explained.

Thank you.