VLOOKUP Anomaly

Hi everyone

I have the formula
<=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(A3&"Column"),2))> in
a cell and it takes the contents of A3 (a country) to interrogate a list and
return the appropriate value. It works fine, unless the country name
contains a space (such as Czech Republic). I tried to change the formula to
have the final <A3> replaced with <(SUBSTITUTE(A3," ",""))> but all that
does is actually display the formula in the cell as well as in the formula
bar. I haven't let an apostrophe sneak in there, nor have I pressed <ALT+¬>
to display the formulae. It is only the formula that I've changed which is
displayed in this way. There are several other formulae in the sheet which
aren't displayed in this way and they all work fine.

I thought of getting around it by having a hidden helper column (column C:C
which has the formula <=SUBSTITUTE(A3," ","")> then have the formula which
is causing the problems refer to a cell in this column (i.e.
......INDIRECT(C3&"Column"),2......) but this also simply displays the actual
formula in the cell. It doesn't give any error message such as #REF! or
#N/A etc. It seems that the <SUBSTITUTE> is somehow messing things up for
me. I have two questions, firstly how can I allow a country name with a
space to be used and secondly, why is the formula being displayed in the
cell, rather than a value or error message?

Thanks for your time

Using the formula with Substitute in the Indirect function should work.

=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))

Is the Czech Republic range named CzechRepublicColumn ?
Perhaps the formula has a space before the equal sign.

Tosca wrote:
> Hi everyone
>
> I have the formula
> <=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(A3&"Column"),2))> in
> a cell and it takes the contents of A3 (a country) to interrogate a list and
> return the appropriate value. It works fine, unless the country name
> contains a space (such as Czech Republic). I tried to change the formula to
> have the final <A3> replaced with <(SUBSTITUTE(A3," ",""))> but all that
> does is actually display the formula in the cell as well as in the formula
> bar. I haven't let an apostrophe sneak in there, nor have I pressed <ALT+¬>
> to display the formulae. It is only the formula that I've changed which is
> displayed in this way. There are several other formulae in the sheet which
> aren't displayed in this way and they all work fine.
>
> I thought of getting around it by having a hidden helper column (column C:C
> which has the formula <=SUBSTITUTE(A3," ","")> then have the formula which
> is causing the problems refer to a cell in this column (i.e.
> .....INDIRECT(C3&"Column"),2......) but this also simply displays the actual
> formula in the cell. It doesn't give any error message such as #REF! or
> #N/A etc. It seems that the <SUBSTITUTE> is somehow messing things up for
> me. I have two questions, firstly how can I allow a country name with a
> space to be used and secondly, why is the formula being displayed in the
> cell, rather than a value or error message?
>
> Thanks for your time
>
>
>
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra

Yes, the range is CezchRepublicColumn and no, there is no leading space!

As a matter of interest, I started a new workbook and set up a dummy
CzechRepublicColumn range and entered the formula into C3. It still
demonstrated the formula in the cell as well as the formula bar, despite no
apostrophe, leading space etc!!!

It's almost as if my version of Excel (2003) doesn't like the combination of
INDIRECT and VLOOKUP.

BTW, I am using the linked lists process that you have explained on your
website so I am pleased that you "jumped in" to help!
..

"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> Using the formula with Substitute in the Indirect function should work.
>
> =IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3,"
> ","")&"Column"),2))
>
> Is the Czech Republic range named CzechRepublicColumn ?
> Perhaps the formula has a space before the equal sign.

Hi again

In further desperation, I saved the change to the formula (which still
generated odd behaviour), then rebooted. Everything works fine now!!! The
laptop is only 6 months old and had been turned on for about 6 hours. I
don't have any virus or malware (or didn't 3 days ago when I last scanned) -
but I'll check again.

Thanks for your help - it was reassuring to know that what I thought should
work, has done, and it seems to have resolved itself.

"Tosca" <[email protected]> wrote in message
news:[email protected]...
> Hi Debra
>
> Yes, the range is CezchRepublicColumn and no, there is no leading space!
>
> As a matter of interest, I started a new workbook and set up a dummy
> CzechRepublicColumn range and entered the formula into C3. It still
> demonstrated the formula in the cell as well as the formula bar, despite
> no apostrophe, leading space etc!!!
>
> It's almost as if my version of Excel (2003) doesn't like the combination
> of INDIRECT and VLOOKUP.
>
> BTW, I am using the linked lists process that you have explained on your
> website so I am pleased that you "jumped in" to help!
> .
>
> "Debra Dalgleish" <[email protected]> wrote in message
> news:[email protected]...
>> Using the formula with Substitute in the Indirect function should work.
>>
>> =IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3,"
>> ","")&"Column"),2))
>>
>> Is the Czech Republic range named CzechRepublicColumn ?
>> Perhaps the formula has a space before the equal sign.
>
>
>

You're welcome, and thanks for describing how you solved the problem
(very mysterious behaviour!). I'm glad you've got it working now.

Tosca wrote:
> Hi again
>
> In further desperation, I saved the change to the formula (which still
> generated odd behaviour), then rebooted. Everything works fine now!!! The
> laptop is only 6 months old and had been turned on for about 6 hours. I
> don't have any virus or malware (or didn't 3 days ago when I last scanned) -
> but I'll check again.
>
> Thanks for your help - it was reassuring to know that what I thought should
> work, has done, and it seems to have resolved itself.
>
> "Tosca" <[email protected]> wrote in message
> news:[email protected]...
>
>>Hi Debra
>>
>>Yes, the range is CezchRepublicColumn and no, there is no leading space!
>>
>>As a matter of interest, I started a new workbook and set up a dummy
>>CzechRepublicColumn range and entered the formula into C3. It still
>>demonstrated the formula in the cell as well as the formula bar, despite
>>no apostrophe, leading space etc!!!
>>
>>It's almost as if my version of Excel (2003) doesn't like the combination
>>of INDIRECT and VLOOKUP.
>>
>>BTW, I am using the linked lists process that you have explained on your
>>website so I am pleased that you "jumped in" to help!
>>.
>>
>>"Debra Dalgleish" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Using the formula with Substitute in the Indirect function should work.
>>>
>>> =IF(OR(ISBLANK(A3),ISBLANK(B3)),"",VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3,"
>>>","")&"Column"),2))
>>>
>>>Is the Czech Republic range named CzechRepublicColumn ?
>>>Perhaps the formula has a space before the equal sign.
>>
>>
>>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"Debra Dalgleish" <[email protected]> wrote...
....
>=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
> VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))
....

Quibble: could replace the OR call with COUNTA(A3,B3)<2.

Thank you Harlan - I'm glad to receive such comments as it makes the formula
less complicated!

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "Debra Dalgleish" <[email protected]> wrote...
> ...
>>=IF(OR(ISBLANK(A3),ISBLANK(B3)),"",
>> VLOOKUP(B3,INDIRECT(SUBSTITUTE(A3," ","")&"Column"),2))
> ...
>
> Quibble: could replace the OR call with COUNTA(A3,B3)<2.
>
>