Why #NA when using VLOOKUP?
 

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

Why #NA when using VLOOKUP?
 

Use

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

--
---
HTH

Bob

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



"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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.

--
HTH

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


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


"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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" wrote in 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.

Thanks, Jim Dixon
"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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" wrote in message ...
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" wrote in 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.

Thanks, Jim Dixon
"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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

"Jim Dixon" wrote in message ...
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" wrote in message ...
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" wrote in 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.

Thanks, Jim Dixon
"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message ...
I think that was clear from your post Jim, hardly warranted any extra comment.

"Jim Dixon" wrote in message ...
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" wrote in message ...
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" wrote in 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.

Thanks, Jim Dixon
"Jim Dixon" wrote in message ...
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

Why #NA when using VLOOKUP?
 

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
"Jim Dixon" escribió en el mensaje ...
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