Im trying to get a value from 6 cells. Im entering the if function into B2.
cells C2, D2, E2, F2, G2 & H2 have either #N/A or the the answer Im looking
for but the answer is not always the same.

If C2 = #N/A then I want it to look in D2, if D2 = #N/A and so on until it
finds the cell that doesnt equal #N/A.

Please help, trying to work this out is doing my head in..........

Thanks,
Kirk

Try this

=INDEX(C2:H2,MATCH(FALSE,ISNA(C2:H2),0))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Kirk" wrote:

Im trying to get a value from 6 cells. Im entering the if function into B2.
cells C2, D2, E2, F2, G2 & H2 have either #N/A or the the answer Im looking
for but the answer is not always the same.

If C2 = #N/A then I want it to look in D2, if D2 = #N/A and so on until it
finds the cell that doesnt equal #N/A.

Please help, trying to work this out is doing my head in..........

Thanks,
Kirk

It works,

I never would have got that formular, I tried to enter it without the
shift+ctrl and it didnt work so I went back and read it.

Thanks for your help.
Kirk

"Mike H" wrote:

Try this

=INDEX(C2:H2,MATCH(FALSE,ISNA(C2:H2),0))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Kirk" wrote:

Im trying to get a value from 6 cells. Im entering the if function into B2.
cells C2, D2, E2, F2, G2 & H2 have either #N/A or the the answer Im looking
for but the answer is not always the same.

If C2 = #N/A then I want it to look in D2, if D2 = #N/A and so on until it
finds the cell that doesnt equal #N/A.

Please help, trying to work this out is doing my head in..........

Thanks,
Kirk

Try this in B2
=INDIRECT("R2C"&LOOKUP(2,1/C2:H2,COLUMN(C:H)),FALSE)

"Kirk" wrote:

Im trying to get a value from 6 cells. Im entering the if function into B2.
cells C2, D2, E2, F2, G2 & H2 have either #N/A or the the answer Im looking
for but the answer is not always the same.

If C2 = #N/A then I want it to look in D2, if D2 = #N/A and so on until it
finds the cell that doesnt equal #N/A.

Please help, trying to work this out is doing my head in..........

Thanks,
Kirk