Why is LOOKUP returning #NA in some fields but not others?

[NyteRyder1]

Here is an example spreadsheet: https://drive.google.com/file/d/0Bwm-ycnD46XcYXlfOVBzVVBrams/view?usp=sharing

Edit to attach file, LOOKUP Example.xlsx

I’m using MIN and MAX to find the highest and lowest values in a range. The ranges include blank cells.

I’m using LOOKUP to find the last value in the list that matches the MIN value, and then I want it to output the value in column A (the date) for the row where the MIN value is found. I’m doing the same thing with the MAX value.

If you look at the example spreadsheet, you’ll see that this works every time for the MAX value (except for ranges with no data), but only sometimes for the MIN value. The second LOOKUP for the MIN value does this in cell C8. It should be looking for the value in C7 (1408). It should find this value in C5 of the Data sheet. It should then output the value in A5 (10/12/16), but it is showing #NA.

Any help will be greatly appreciated. Thanks!

I clicked on my own link to make sure it was viewable. The link itself didn’t show the sheet, but gave the option to open it in Google Sheets. When I opened it in Google Sheets, the formulas always return the date and never return #NA.

OS: Windows 10 Pro w/Anniversary Update (10.0.14393 Build 14393)
LibreOffice: 5.1.5.2 (x64)

[mark_t]

Edited the question to attach the file.

[mark_t]

Note Excel also shows the same problem with LOOKUP.

[Lupp]

From the 'Help’concerning the LOOKUP function:
Additionally, the search vector for the LOOKUP must be sorted in ascending order, otherwise the search will not return any usable results.

(LOOKUP does not support an additional parameter to explicitly make assurances concerning the order. It always assumes the search vector sorted ascending.)

Solution (exemplified for Info.C8): =INDEX(DataDate;MATCH(C7;$Data.C4:C1048576;0))

I would generally prefer a combination of MATCH and INDEX or OFFSET over LOOKUP as I try to get formulae explicitly telling what they were made for. It should also be advisable to reduce the search range with respect to the maximum row number. Is $10001 (< 1% of what you used) enough? If not, spreadsheets might not be the appropriate tool for the task.

[NyteRyder1]

It doesn’t have to be to row 10,000. I doubt the data will ever go that far. Unless that causes issues with the spreadsheets though, I like to be all-encompassing. Thanks for the INDEX formula though. That works like a charm!

[Lupp]

There are cases where MATCH and/or LOOKUP will actually search the complete range, IMO. Efficiency may break down due to unneded large ranges then. I would prefer to parametrise the sizes anyway.

[mark_t]

I don’t know why LOOKUP is not giving the correct result in some cases but you could try the following combination of INDEX and MATCH.

Example formula for cell C8 of your spreadsheet

=INDEX(DataDate,MATCH(C7,Data.C4:C1048576,0))

[NyteRyder1]

Thanks! That works like a charm!

[mark_t]

I think @Lupp had the correct answer as he explained why LOOKUP was not working.