Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLookup problem
Hi All,
I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
#2
|
|||
|
|||
Is the data imported, especially from a website?
Regards, Alan "K" wrote in message ... Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
#3
|
|||
|
|||
I would also try the trim function to insure there are no spaces.
A way to check it also is to look at two cells one what gets entered in the lookup value cell (A1) and the other one that looks like that value that you would expect to be selected by the Vlookup (A40) in a blank cell enter =A1=A40 if the answer is false there could be several things try =len(A1)-Len(A40) =isnumber(A1) =isnumber(A40) because something does not match "K" wrote: Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
#4
|
|||
|
|||
Yup - imported from a billing system.
"Alan" wrote: Is the data imported, especially from a website? Regards, Alan "K" wrote in message ... Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
#5
|
|||
|
|||
K wrote:
Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! Post your formula. Alan Beban |
#6
|
|||
|
|||
Good call, the data in the lookup value cell is one space shorter than the
data in the array. How do I figure out where that space is and remove it? "bj" wrote: I would also try the trim function to insure there are no spaces. A way to check it also is to look at two cells one what gets entered in the lookup value cell (A1) and the other one that looks like that value that you would expect to be selected by the Vlookup (A40) in a blank cell enter =A1=A40 if the answer is false there could be several things try =len(A1)-Len(A40) =isnumber(A1) =isnumber(A40) because something does not match "K" wrote: Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
#7
|
|||
|
|||
IT WORKED! I used the trim function and it solved my problem! Thanks so
much - it's been 2 wasted days on this! "bj" wrote: I would also try the trim function to insure there are no spaces. A way to check it also is to look at two cells one what gets entered in the lookup value cell (A1) and the other one that looks like that value that you would expect to be selected by the Vlookup (A40) in a blank cell enter =A1=A40 if the answer is false there could be several things try =len(A1)-Len(A40) =isnumber(A1) =isnumber(A40) because something does not match "K" wrote: Hi All, I'm trying to fill in a retail price based on the product code provided in a neighboring column. That product code is being pulled via a lookup function and works fine. The price on the other hand gives me the dreaded #N/A, even though I can copy the product code and do an alt-F in the table array to find the exact match. I added the text() function within my VLOOKUP, and verified that it's searching against text. If I change the range_lookup to TRUE, it uses the code directly above the correct one in the table array and gives me the price for that one instead. I'm tearing my hair out because I can't figure out what's wrong with it! Please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Discussion (Misc queries) | |||
Problem with VLOOKUP and drop-down lists! | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Worksheet Functions | |||
Excel Problem: VLookup | Excel Worksheet Functions |