Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP
=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). Glen Mettler wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |