| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
