#N/A error with Lookup and Min functions

[dallen8028]

Good Afternoon everyone,

I have run into an issue using Lookup with Min. I can use Lookup with Max and get the required results, =LOOKUP(MAX(E7:E1000),E7:E1000,D7:D1000) actually gives the date that matches the Max value. When I change the formula to =LOOKUP(MIN(E7:E1000),E7:E1000,D7:D1000), it returns #N/A. I am looking for the date that matches the Min value. I noticed that there are more than one Min value (which is probably giving me the #N/A error). I do not know if this is actually the case and I do not know how to resolve it. Would someone be able to steer me in the right direction?

Thank you in Advance.

 

[joeu2004]

It is okay for there to be more than one match; just understand that you cannot predict which one LOOKUP will choose.

Be sure that E7:E1000 is in ascending order. Otherwise, do the following:

=INDEX(D7:D1000, MATCH(MIN(E7:E1000),E7:E1000,0), 1)

Likewise when using MAX. LOOKUP(MAX...) might worked only by coincidence if E7:E1000 is not in ascending order.

 

[dallen8028]

It is okay for there to be more than one match; just understand that you cannot predict which one LOOKUP will choose.

Be sure that E7:E1000 is in ascending order. Otherwise, do the following:

=INDEX(D7:D1000, MATCH(MIN(E7:E1000),E7:E1000,0), 1)

Likewise when using MAX. LOOKUP(MAX...) might worked only by coincidence if E7:E1000 is not in ascending order.

Thank you for your response Joeu2004, Your Formula worked great!

Still don't understand why the other didn't regarding the ascending order. The dates in Column D are Oldest to Newest. The numbers that are being looked at in Column E are random as they change daily. Does this make a difference?

 

[joeu2004]

The numbers that are being looked at in Column E are random as they change daily. Does this make a difference?

No. I wrote: ``Be sure that E7:E1000 is in ascending order``.

Did the INDEX/MATCH formula work for you?

 

[dallen8028]

Yes it did. thank you!

 

[joeu2004]

Still don't understand why the other didn't regarding the ascending order.

Sorry: I misread your question. As I wrote: ``LOOKUP(MAX...) might [work] only by coincidence``. LOOKUP(MIN...) might work by coincidence, as well.

LOOKUP does a binary search. Go to the midpoint of rows 7:1000 (504). Step 2: If it equals what we want, we're done. If what we want is less than #504 , go to the midpoint of 7:503 (255), and go to Step 2. If what we want is greater than #504 , go to the midpoint of 504:1000 (752), and go to Step 2. If what we want is less than #7 , return #N/A. If it is greater than #1000 , return #1000 .

That algorithm works properly only if the data that we search is in ascending order. Otherwise, the results are unpredictable.