VLOOKUP error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Solitary - 14 Sep 2007 04:48 GMT

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

Reply to this Message

Max - 14 Sep 2007 05:48 GMT

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,FALSE)
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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> =VLOOKUP(A2,Budget!$C$2:$Z$10000,6,FALSE)
>
[quoted text clipped - 3 lines]
> reflect the correct value. Just cannot seem to get it to work when I use the
> Autofill to copy the formula downwards.

Reply to this Message

James - 14 Sep 2007 12:06 GMT

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

Signature

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

> 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
[quoted text clipped - 19 lines]
> > reflect the correct value. Just cannot seem to get it to work when I use the
> > Autofill to copy the formula downwards.

Reply to this Message

Max - 14 Sep 2007 14:30 GMT

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>

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message