Why #NA when using VLOOKUP?

Use

=VLOOKUP(MIN(A1:A5),A1:B5,1,FALSE)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

Try it with zero or FALSE as the last argument in the VLOOKUP()

If 1 or TRUE is used as the 4th argument then the list needs to be sorted in assending order.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

To Bob and Sandy, and anyone else who answers this question...MANY, MANY THANKS!!! WHOOPEE!!! YIPPEE!!! ...etc. etc. etc.

Now that it works, I can go on, and try to answer my unwritten question...why? No, don't answer it for me, it would ruin all the fun I'll have when I have the "Duh!!" moment.

I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

T. Valko - 17 Jul 2007 01:21 GMT

>when I have the "Duh!!" moment.

Like when you figure out that all you need is:

=MIN(A1:A5)

Signature

Biff
Microsoft Excel MVP

To Bob and Sandy, and anyone else who answers this question...MANY, MANY THANKS!!! WHOOPEE!!! YIPPEE!!! ...etc. etc. etc.

Now that it works, I can go on, and try to answer my unwritten question...why? No, don't answer it for me, it would ruin all the fun I'll have when I have the "Duh!!" moment.

Thanks, Jim Dixon
"Jim Dixon" <jimd@teoil.com> wrote in message news:OPKjce9xHHA.3564@TK2MSFTNGP06.phx.gbl...
I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

Jim Dixon - 17 Jul 2007 14:37 GMT

I did know MIN would work, what I left out was I also wanted the value associated with the lowest cost, from column B, so I was using a VLOOKUP for both of them. But thanks, nonetheless, I appreciate your and everyones help.
Jim

>when I have the "Duh!!" moment.

Like when you figure out that all you need is:

=MIN(A1:A5)

--
Biff
Microsoft Excel MVP

"Jim Dixon" <jimd@teoil.com> wrote in message news:udWHfz9xHHA.748@TK2MSFTNGP04.phx.gbl...
To Bob and Sandy, and anyone else who answers this question...MANY, MANY THANKS!!! WHOOPEE!!! YIPPEE!!! ...etc. etc. etc.

Now that it works, I can go on, and try to answer my unwritten question...why? No, don't answer it for me, it would ruin all the fun I'll have when I have the "Duh!!" moment.

Thanks, Jim Dixon
"Jim Dixon" <jimd@teoil.com> wrote in message news:OPKjce9xHHA.3564@TK2MSFTNGP06.phx.gbl...
I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

Bob Phillips - 17 Jul 2007 15:12 GMT

I think that was clear from your post Jim, hardly warranted any extra comment.

I did know MIN would work, what I left out was I also wanted the value associated with the lowest cost, from column B, so I was using a VLOOKUP for both of them. But thanks, nonetheless, I appreciate your and everyones help.
Jim

"T. Valko" <biffinpitt@comcast.net> wrote in message news:uaAFghAyHHA.1164@TK2MSFTNGP02.phx.gbl...
>when I have the "Duh!!" moment.

Like when you figure out that all you need is:

=MIN(A1:A5)

--
Biff
Microsoft Excel MVP

"Jim Dixon" <jimd@teoil.com> wrote in message news:udWHfz9xHHA.748@TK2MSFTNGP04.phx.gbl...
To Bob and Sandy, and anyone else who answers this question...MANY, MANY THANKS!!! WHOOPEE!!! YIPPEE!!! ...etc. etc. etc.

Now that it works, I can go on, and try to answer my unwritten question...why? No, don't answer it for me, it would ruin all the fun I'll have when I have the "Duh!!" moment.

Thanks, Jim Dixon
"Jim Dixon" <jimd@teoil.com> wrote in message news:OPKjce9xHHA.3564@TK2MSFTNGP06.phx.gbl...
I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

T. Valko - 17 Jul 2007 18:46 GMT

Well then, guess I just had a Duh! moment!

Signature

Biff
Microsoft Excel MVP

I think that was clear from your post Jim, hardly warranted any extra comment.

"Jim Dixon" <jimd@teoil.com> wrote in message news:eHcojdHyHHA.1576@TK2MSFTNGP03.phx.gbl...
I did know MIN would work, what I left out was I also wanted the value associated with the lowest cost, from column B, so I was using a VLOOKUP for both of them. But thanks, nonetheless, I appreciate your and everyones help.
Jim

"T. Valko" <biffinpitt@comcast.net> wrote in message news:uaAFghAyHHA.1164@TK2MSFTNGP02.phx.gbl...
>when I have the "Duh!!" moment.

Like when you figure out that all you need is:

=MIN(A1:A5)

--
Biff
Microsoft Excel MVP

"Jim Dixon" <jimd@teoil.com> wrote in message news:udWHfz9xHHA.748@TK2MSFTNGP04.phx.gbl...
To Bob and Sandy, and anyone else who answers this question...MANY, MANY THANKS!!! WHOOPEE!!! YIPPEE!!! ...etc. etc. etc.

Now that it works, I can go on, and try to answer my unwritten question...why? No, don't answer it for me, it would ruin all the fun I'll have when I have the "Duh!!" moment.

Thanks, Jim Dixon
"Jim Dixon" <jimd@teoil.com> wrote in message news:OPKjce9xHHA.3564@TK2MSFTNGP06.phx.gbl...
I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message

Hi Jim

VLOOKUP is a lookup and reference functions that works with value in ascending order. Please sort column A and thats all.

Good look
I'm trying to use VLOOKUP to find lowest value in a small group. The exact sample is below:

A B
1 3.0001 A
2 2.9442 B
3 2.9610 C
4 2.9055 D
5 2.9630 E

The formula I'm using is =VLOOKUP(MIN(A1:A5),A1:B5,1) . I'm trying to get it to return the lowest cost, from column A. If I take out Row 4, it works. But with Row 4 in it, it returns a value of #N/A, I'm guessing from the HELP screen it's because "...lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error..."

Sorry, don't get it. Anyone feels like helping a noobie get it thru his skull?

Thanks,
Jim Dixon

Reply to this Message