Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
=VLOOKUP(A2,Budget!$C$2:$Z$10000,6,FALSE)
This is the formula that I am entering into my spreadsheet. However, when I copy it downwards, I get a #N/A, even though there is a match for the lookup value in the table. If I enter the formula in the next cell, Excel will reflect the correct value. Just cannot seem to get it to work when I use the Autofill to copy the formula downwards. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
Data inconsistency is the usual culprit. Probably the values within the
lookup col C in "Budget" are text numbers, while the lookup values in A2 down are real numbers Try: =VLOOKUP(A2&"",Budget!$C$2:$Z$10000,6,FALSE) which will convert the real numbers in A2 down to text numbers If you need to add leading zeros in converting it to text numbers, try instead something like: =VLOOKUP(TEXT(A2,"0000"),Budget!$C$2:$Z$10000,6,FA LSE) Adjust the "0000" part to suit I disregarded your comment below, which was confusing to me: If I enter the formula in the next cell, Excel will reflect the correct value. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Solitary" wrote: =VLOOKUP(A2,Budget!$C$2:$Z$10000,6,FALSE) This is the formula that I am entering into my spreadsheet. However, when I copy it downwards, I get a #N/A, even though there is a match for the lookup value in the table. If I enter the formula in the next cell, Excel will reflect the correct value. Just cannot seem to get it to work when I use the Autofill to copy the formula downwards. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
Max -
I think they meant that if they enter the formula themselcves then the lookup function works, but if they use the autofill 'tool' it doesn'twork. Solitary- The only thing I can think of is to make sure that you're just copying it and not increasing it in series (i.e. going A2 to A3 to A4 etc). try a simple copy and paste see if that works. Cheers, -James -- "People are strange like that. Steal five pounds and you''''re a petty thief. But steal twenty thousand pounds and you''''re either a hero or a government." "Max" wrote: Data inconsistency is the usual culprit. Probably the values within the lookup col C in "Budget" are text numbers, while the lookup values in A2 down are real numbers Try: =VLOOKUP(A2&"",Budget!$C$2:$Z$10000,6,FALSE) which will convert the real numbers in A2 down to text numbers If you need to add leading zeros in converting it to text numbers, try instead something like: =VLOOKUP(TEXT(A2,"0000"),Budget!$C$2:$Z$10000,6,FA LSE) Adjust the "0000" part to suit I disregarded your comment below, which was confusing to me: If I enter the formula in the next cell, Excel will reflect the correct value. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Solitary" wrote: =VLOOKUP(A2,Budget!$C$2:$Z$10000,6,FALSE) This is the formula that I am entering into my spreadsheet. However, when I copy it downwards, I get a #N/A, even though there is a match for the lookup value in the table. If I enter the formula in the next cell, Excel will reflect the correct value. Just cannot seem to get it to work when I use the Autofill to copy the formula downwards. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
James, you may be right
Guess my focus was more on this part of the post .. when I copy it downwards, I get a #N/A, even though there is a match for the lookup value in the table. which implied (to me) that the OP knew how to copy formulas down but was hitting the bricks on the #N/A returns. Perhaps there's a chance? that the OP will return and clarify <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup error | Excel Discussion (Misc queries) | |||
vlookup error | Excel Worksheet Functions | |||
VLookup N/A Error | Excel Discussion (Misc queries) | |||
vlookup error!! | Excel Worksheet Functions | |||
#N/A error with VLOOKUP | Excel Discussion (Misc queries) |