Why #NA when using VLOOKUP?

  • Thread starter Thread starter Jim Dixon
  • Start date Start date
J

Jim Dixon

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
 
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)



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
 
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

(e-mail address removed)
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
 
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
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
 
when I have the "Duh!!" moment.

Like when you figure out that all you need is:

=MIN(A1:A5)

--
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
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
 
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 said:
when I have the "Duh!!" moment.
Click to expand...

Like when you figure out that all you need is:

=MIN(A1:A5)

--
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
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
 
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 said:
when I have the "Duh!!" moment.
Click to expand...

Like when you figure out that all you need is:

=MIN(A1:A5)

--
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
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
 
Well then, guess I just had a Duh! moment!

--
Biff
Microsoft Excel MVP


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 said:
when I have the "Duh!!" moment.
Click to expand...

Like when you figure out that all you need is:

=MIN(A1:A5)

--
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
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
 
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" <[email protected]> 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
 
Back
Top