LOOKUP in OpenOffice sometimes retruns error

Topic

New Reply

I have an OpenOffice spreadsheet which has names in col A and numbers in column C of rows 24-40. In row 43, I show the lowest number found in rows 24-40, and in row 44, I show how many times that lowest number appeared.

In row 45, if the lowest number (row 43) only appeared once (col 44) in rows 24-40, I want to show the corresponding value from col A.

Here is my formula:

=IF(C44=1;LOOKUP(C43;C24:C40;$A24:$A40);””)

I get a blank if the lowest number is not unique (C44 1), but the problem is if the number is unique, this works sometimes, and sometimes I get #N/A (Error: Value not available). I can’t figure out why I sometimes get the error.

Any ideas would be greatly appreciated.

Thanks … John

Reply | Quote

Replies

I guess the answer is that the data in the search range needs to be in ascending order to get useful results. In my case, it is not. Don’t think I can use VLOOKUP either. Hmmmm.

Thanks … John

Reply | Quote

I guess the answer is that the data in the search range needs to be in ascending order to get useful results. In my case, it is not. Don’t think I can use VLOOKUP either. Hmmmm.

Thanks … John

Try…

=IF(C44=1;INDEX($A24:$A40;MATCH(C43;C24:C40;0));””)

Reply | Quote

If sorting the data is not an option, Col A could be copied/formula to Col D ( or some other column to right of Col C) and then use VLOOKUP.

Reply | Quote

Thanks, that’s exactly what I did!

Reply | Quote