Vlookup Problem

[Uamdb]

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

 

[Aladin Akyurek]

What are the results of:

=ISNUMBER(S10)

and

=ISNUMBER(TheCellInClientThatIsEqualToTheValueInS10)

 

[sneel3]

Instead of placing "Client" in the formula, try it with the range of the cells instead (i.e., $A:$B)

 

[Uamdb]

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]

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.