Vlookup - is there a maximum

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

Is there a maximun range that a Vlookup can handle?

I have Worksheet1 that has about 15000 records. The
vlookup table is on Worksheet2 and has about 2200 records.

The vlookup range will also have to be 8 column wide. I
can't seem to get it to work. is it because of the size?
 
Hi
should work (though maybe a little bit slow). What kind of error did
you get?
You may also post your non working VLOOKUP formula so we can helpt you
finding the error
 
I don't get an error message so much as N/A or the wrong
data coming in.

=VLOOKUP(D7,Vlookup!B5:J2154,2)

In this formula, D7 holds a SIN #, the Vlookup table 1
column has SIN #, and column 2 has the clients account #.
I am getting unmatching criteria.

Is it possible that this is because I don't have SIN #
available for all clients? or that I am in Excel 97. Also
worth noting is the file have been created from a Crystal
export.

Thanks
 
Hi
try
=VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0)
you need the last aparemter for finding exact matches in an unsorted
list

To prevent the error (for non matches) change the above to
=IF(ISNA(VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0)),"no match
found",VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0))
 
Thank you. That seems to be it!
-----Original Message-----
Hi
try
=VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0)
you need the last aparemter for finding exact matches in an unsorted
list

To prevent the error (for non matches) change the above to
=IF(ISNA(VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0)),"no match
found",VLOOKUP(D7,Vlookup!$B$5:$J$2154,2,0))


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Back
Top