Vlookup Problem

Uamdb

New Member
Joined
Aug 17, 2006
Messages
10
Hello once again.

I am having a problem with my VLOOKUP. I have a two colum chart A1 have a clients Number and A2 has the client Name.

On a second sheet I have formula where I enter the Client Number and the Name fills in.

=IF(ISNA(VLOOKUP(S10,Client,2,false))," ",VLOOKUP(S10,Client,2,false))
The chart is named "Client"
Everything works fine until I enter a client number greater than 1000.
Both columns are formated as text. So if I enter client number 0098 I get a correct response. If I enter 1101 I get no response. The numbers are in order and there are gaps in the numbers through out the chart.
If anyone could help
Thanks
David
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
What are the results of:

=ISNUMBER(S10)

and

=ISNUMBER(TheCellInClientThatIsEqualToTheValueInS10)
 

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
Instead of placing "Client" in the formula, try it with the range of the cells instead (i.e., $A:$B)
 

Uamdb

New Member
Joined
Aug 17, 2006
Messages
10
When I pasted the two formulas I get a false result, and I tried replacing "client" with the range Client2!A1:B3936

Is there a limit on the size of a Vlookup?
 

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
There is no size limit. Are your ranges locked before copying your formula? Try it again with Client2!$A$1:$B$3936 and with $S10 if you are copying the formula down a column. Also, make sure, as Aladin mentioned above that all items in column S you are looking up from and all items in column A you are looking up to are in the same format (this does not just mean Format/Cells/Text, it means write a formula in another cell that says =text(S10,"@") and one that says =text(a1,"@") and copy the formula down to all cells and do the lookup from that new cell or copy and paste it over the old cells and see if the vlookup works.
 

Forum statistics

Threads
1,136,878
Messages
5,678,326
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top