It's all a matter of making it relative ...
Assume the source data is within say: B7:D10
In F7: 2 (input the col number, eg 2nd col = 2)
In F8: 6 (input the value in 2nd col to be found)
Then in F9, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=MAX(IF(OFFSET(B7:B10,,F7-1)=F8,ROW(1:4)))
Key adaptation points to note:
B7:B10 = the leftmost col range of the source data
4 = number of rows in the source data
A working example of the above is he
http://cjoint.com/?myahwyzy6w
Voila? celebrato, hit the YES below
--
Max
Singapore
---
"engineer" wrote:
Max,
It does not work. I think the confusion may lie in the fact that "A" is the
value in Cell "A1", "B" is the value in Cell "B1", etc. You've assumed my
column titles are the Excel column files. It probably would have been
clearer if I used numbers or "X", "Y", "Z" as my column titles. So maybe I
didn't pose the question clear enough. Valko is correct, in that I am
wanting the row number even if the table is shifted down so many rows. So
three would still be the answer no matter how far the table is shifted down.