Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List
Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.
The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).
It returns “Black Onyx” because that is what is in cell C15, corresponding to the fact that cell A15 contains the last number (a time, but still a number) in column A of that list.
The formula in cell F2 is =INDEX(A3:D14,MATCH(REPT("z",255),A3:A14,1),4).
It returns “4:04” because that is what is in cell D14, corresponding to the fact that cell A14 contains the last text item in column A.
In above example what is the role of Rept(“z”, 255)to find last Text in data range.
The way the LOOKUP function works is, where the value to be found is larger than any value of that type (numeric or text) it returns the last listed item of that type. In my example, the last text item is being searched for. REPT(“Z”,255) creates a series of 255 Z’s, which is sure to be “larger” than any other text value. Therefore, LOOKUP returns the last listed text value.
Suppose in cells A1:A5 you have this data:
Col A
1 Maureen
2 500
3 Tom
4 366
5 984
The formula =LOOKUP(REPT(“z”,255),A1:A5) will return Tom because Tom is the last text item in that list.
For the last number, the formula =LOOKUP((10^10),A1:A5) would return 984.
Thanks Tom, ☺