Help needed with lookup tables..

S

Stuart Maclean

posted on behalf of a friend..so sorry if any mistakes..

was wondering if you could post this on to a excel forum.. its driving me
round the bend and i cant manually do it this time becasue the spreadsheet
this is from has 64000 rows and i cant go through each one.

right i will try and explain this the best i can..

1) I have a lookup table which has BL_NO's in one cell and the month in the
cell next to it. e.g 781900109 SEP
2) i have a spreadsheet which has 64000 rows with bill numbers being in the
first column.
3) I have done a VLOOKUP to search for the bill numbers in my lookup table
with a purpose of putting the corresponding month in the month column in my
main data sheet. (as below).
4) however when i copy and paste the formula down the entire spreadsheet,
every row comes up with N/A (as below).
5) however i know that the bills in the lookup table are in the spreadsheet
so it should give me the month of "sep" in the month column for the bills
in question, but it doesnt.
6) BUT when i search for the bill numbers in the main spreadsheet and
double click the cell with the bill number in, it then changes the month
from N/A to Sep.
7) why does double clicking the lookup value cell enable the formula to
work.
8) this is the lookup formula i am using:
=VLOOKUP(A49480,Sheet2!$A$1:$B$103,2,0)
9) theres no way i can go through 64000 rows and double click each lookup
values.

screenshot can be seen here -
http://romseylionsclub.hampshire.org.uk/screenshot.jpg

please help.
 
M

Max

It sounds like the calc mode's accidentally set to Manual.
Click Tools > Options > Calculation tab
Ensure Automatic is checked > OK
 
S

Stuart Maclean

hi there apparently that didn't solve the problem - any other thoughts..
many Thanks for your help!!
 
M

Max

Sorry, think I misread it earlier

Try using a little coercion on the lookup value:
=VLOOKUP(A49480+0,Sheet2!$A$1:$B$103,2,0)
The "+0" will coerce text number to a real number for correct matching
 
S

Stuart Maclean

Excellent - apparently that worked.. thanks for your help on this one...
Regards
Stuart
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top