find the last cell with a certain value and...

[deLockloire]

hi,

i'd like a formula, which finds the last "X" value in an array, e.g., C1:C30 (and it is possible that more cells in this column have the "X" value, but i need the very last one) and return the value of a cell from the same row in column A. So, if im looking for "X" and C1, C5, C8 and C9 has "X" as their values, I need to get the value of A9. could you help me out with this?

 

[Haseeb Avarakkan]

Hello, Try

=LOOKUP(2,1/(C1:C30="X"),A1:A30)

Do not use the whole column reference, like C:C, A:A. Use a range C1:C1000....

 

[Aladin Akyurek]

hi,

i'd like a formula, which finds the last "X" value in an array, e.g., C1:C30 (and it is possible that more cells in this column have the "X" value, but i need the very last one) and return the value of a cell from the same row in column A. So, if im looking for "X" and C1, C5, C8 and C9 has "X" as their values, I need to get the value of A9. could you help me out with this?

Try...

Either just enter:

=LOOKUP(9.99E+307,SEARCH("X",C1:C30),A1:A30)

Or control+shift+enter, not just enter:

=LOOKUP(9.99E+307,IF(C1:C30="X",1),A1:A30)

 

[deLockloire]

thanks. it works like a charm.