Vlookup / Search
Posted by phil on December 17, 2001 8:01 AM
is there a formula that can find the last cell value in a spreadsheet....
e.g.
i'm in cell U28 and i need to find the last cell reference for collum i that equals n1 and is above the current row (28 in this case)
thanxs
phil
Posted by Aladin Akyurek on December 17, 2001 9:33 AM
Phil --
I reckon n1 is a number (and is probably in some cell).
If I'm understanding you correctly, the following formula entered in U28
=SUMPRODUCT(MAX((I1:I27=6)*ROW(I1:I27)))
where n1 can be a number or cell ref that contains that number (if it's text, it's OK for it to be in a cell, otherwise you need to place it in between double quotes in the above formula), will give you the row number of the last cell of interest being equal to n1. And,
=ADDRESS(=SUMPRODUCT(MAX((I1:I27=6)*ROW(I1:I27))),9)
the address of that cell.
Hope I understood it as you intended.
Aladin
==========
Posted by Aladin Akyurek on December 17, 2001 3:18 PM
Phil --
The last formula has an extraneous "=", slipped in by copying and pasting no doubt. It should be:
=ADDRESS(SUMPRODUCT(MAX((I1:I27=6)*ROW(I1:I27))),9)
Aladin
=====
