Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List
  1. Rajesh Sinha says:

    In above example what is the role of Rept(“z”, 255)to find last Text in data range.

    • Tom Urtis says:

      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.

  2. RAJESH SINHA says:

    Thanks Tom, ☺

Leave a Reply to Rajesh Sinha Cancel reply

Your email address will not be published. Required fields are marked *

*