VLOOKUP

[Glen Mettler]

I have a Vlookup in a sheet like this:
=VLOOKUP($B3,Events!$A$2:$C$21,3,0)
where $B3 is a value from 01 to 21 (entered by user)
In the remote sheet I create the lookup number with code like this:
=IF(A10+1<10,CONCATENATE("0",A10+1),A10+1) to get a text value (ie
01,02,03...21)

The problem - for all values less than 10, it works fine. For values of 10
and above I get #N/A
However, when I change the formula (in the remote sheet) for 10 (or above)
to hard coded text (for 10 it would be '10), I get the correct return

The cell type is the same for source and remote (text).

Anybody know why? Is there a workaround besides hard coding the > 10
values?

Glen

 

[Dave Peterson]

=Concatenate("0",A10+1) returns a string.

If your data should be two character strings, you could use this:

=text(a10+1,"00")

If your data should be numeric (but show two characters), then just:

=a1+1
but format|cells|number tab|custom
In the type box: 00

It looks like you'll want the last option--since your value in B3 looks like
it's a number (not string).