VLOOKUP Mystery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RHubbard - 20 Sep 2005 16:27 GMT

One of the Excel users here at UM has encountered a bizarre problem.

He is generating a report from our accounting system which he paste
into an excel spreadsheet. He is using VLOOKUP to get data from th
pasted report which is used to update MTD and YTD accounts.

In general, it works fine except for this oddity:

In account numbers that are structured as XXXXX-XX (where X is
numeric digit), the VLOOKUP function works fine. In some situiation
where account numbers are structured without a suffix XXXXXX VLOOKU
will not return information UNLESS the users goes to the target shee
and simply clicks in the cell that should be updated. At that point
the data comes across. Whats Gives?

Thanks for the help.

Ric

Reply to this Message

David McRitchie - 20 Sep 2005 17:46 GMT

simply clicks? or is it F2 then Enter

Check to make sure that calculation is turned set on Automatic
Tools, Calculation (tab), calculation: [x] Automatic

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> One of the Excel users here at UM has encountered a bizarre problem.
>
[quoted text clipped - 14 lines]
>
> Rick

Reply to this Message

Conrad Carlberg - 20 Sep 2005 17:57 GMT

Hi Rick,

I assume that XXXXX-XX is the lookup value -- that is, the user plugs in an
account number to return via VLOOKUP a MTD or YTD figure. If that
assumption's wrong, please correct me.

What is the VLOOKUP function returning before the user clicks in its cell?
Nothing? An error value? (BTW, you might find the Tools | Formula Auditing |
Evaluate Formula tool useful here, if you have a sufficiently recent version
of Excel.) It shouldn't matter, but you might want to check how he has the
Calculate option set. Manual? Automatic?

Hard to tell yet what's going on, but:

XXXXX-XX

is a text value, and

XXXXX

given that X = numeric digit is a number. Again, though, this shouldn't make
a difference.

In the XXXXX-XX case, is there an exact match in the lookup table? What
range_lookup option is he using?

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005

> One of the Excel users here at UM has encountered a bizarre problem.
>
[quoted text clipped - 20 lines]
> RHubbard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27406
> View this thread: http://www.excelforum.com/showthread.php?threadid=469192

Reply to this Message

Ron Rosenfeld - 20 Sep 2005 18:43 GMT

>One of the Excel users here at UM has encountered a bizarre problem.
>
[quoted text clipped - 14 lines]
>
>Rick

Is XXXXXX a number? If so, it may have been pasted as TEXT, where VLOOKUP is
looking for a number (or vice versa).

--ron

Reply to this Message