Why #NA when using VLOOKUP?

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
 
B

Bob Phillips

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
 
S

Sandy Mann

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
 
J

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
 
T

T. Valko

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
 
J

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
 
B

Bob Phillips

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
 
T

T. Valko

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
 
E

Estaylin Rubio

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Trying to do a vlookup with a nested right function on the array 3
vlookup not working with defined name 3
LOOKUP VALUE OCCURS >ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE 6
VLookup formula transposing table_array when copied to another cel 1
VLOOKUP problem 3
Possible to combine VLOOKUP and IF(AND) functions? 1
Returning Multiple Results with Vlookup 1
Bugs in vlookup? 0

Top