**Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers**

Here are several examples rolled into one screen shot that show how to:

• Return the minimum and maximum numbers in a list.

• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.

• Lookup in reverse (to the left) of the aforementioned numbers in a table.

The formula in cell G2 is `=INDEX(A2:A27,MATCH(MIN(D2:D27),D2:D27,0))`

The formula in cell H2 is `=INDEX(B2:B27,MATCH(MIN(D2:D27),D2:D27,0))`

The formula in cell I2 is `=INDEX(C2:C27,MATCH(MIN(D2:D27),D2:D27,0))`

The formula in cell J2 is `=MIN(D2:D27)`

The formula in cell G3 is `=INDEX(A2:A27,MATCH(SMALL(D2:D27,2),D2:D27,0))`

The formula in cell H3 is `=INDEX(B2:B27,MATCH(SMALL(D2:D27,2),D2:D27,0))`

The formula in cell I3 is `=INDEX(C2:C27,MATCH(SMALL(D2:D27,2),D2:D27,0))`

The formula in cell J3 is `=SMALL(D2:D27,2)`

The formula in cell G4 is `=INDEX(A2:A27,MATCH(SMALL(D2:D27,3),D2:D27,0))`

The formula in cell H4 is `=INDEX(B2:B27,MATCH(SMALL(D2:D27,3),D2:D27,0))`

The formula in cell I4 is `=INDEX(C2:C27,MATCH(SMALL(D2:D27,3),D2:D27,0))`

The formula in cell J4 is `=SMALL(D2:D27,3)`

The formula in cell G7 is `=INDEX(A2:A27,MATCH(MAX(D2:D27),D2:D27,0))`

The formula in cell H7 is `=INDEX(B2:B27,MATCH(MAX(D2:D27),D2:D27,0))`

The formula in cell I7 is `=INDEX(C2:C27,MATCH(MAX(D2:D27),D2:D27,0))`

The formula in cell J7 is `=MAX(D2:D27)`

The formula in cell G8 is `=INDEX(A2:A27,MATCH(LARGE(D2:D27,2),D2:D27,0))`

The formula in cell H8 is `=INDEX(B2:B27,MATCH(LARGE(D2:D27,2),D2:D27,0))`

The formula in cell I8 is `=INDEX(C2:C27,MATCH(LARGE(D2:D27,2),D2:D27,0))`

The formula in cell J8 is `=LARGE(D2:D27,2)`

The formula in cell G9 is `=INDEX(A2:A27,MATCH(LARGE(D2:D27,3),D2:D27,0))`

The formula in cell H9 is `=INDEX(B2:B27,MATCH(LARGE(D2:D27,3),D2:D27,0))`

The formula in cell I9 is `=INDEX(C2:C27,MATCH(LARGE(D2:D27,3),D2:D27,0))`

The formula in cell J9 is `=LARGE(D2:D27,3)`

## Leave a Reply