built-in functions can't do that. Stated otherwise, it's not the way spreadsheets operate.
Aladin
========
Thanks for your help mate.
There is another way I can get around it. I will try and describe the situation with a little graphic.
A.....B.....C
......50
......60
Y.....70
......80
............#
(Ignore the full stops, I wasnt sure if the graphic would appear correctly, so I added the full stops to help the appearance.)
I want c5 to equal b3 whenever a3="Y". If the Y was in a2 then c5 would equal 60. etc. C5 should also only be triggered by the first Y in column A. So a Y in a4 would still produce a 70 in c5. But c5 needs to remain in c5 and all cells above it must remain free of formulas.
Sorry, and the area I want to use it in extends beyond the 5 rows, so using a nested If statement will not work due to its limitations.
Sam --
It makes now more sense (to me).
If we have in A1:B4
{"",50;
"",60;
"Y",70;
"",80}
where "" stands for empty cells,
enter just in C5: =INDEX(B1:B4,MATCH("Y",A1:A4,0))
This will give you the value that corresponds to "Y" in B. If there is no "Y" in A, you'll get #N/A.
Is this what you're looking for?
Aladin
==============
Mate you are a legend!
Thanks a lot, that worked a treat. I am very appreciative.