Hello,

I get a name error when I try to use this formula:

=IF(A4="Q1",U10,IF(A4="Q2",U11,IF(A4="Q3",U12,IF(A 4="Q4",U13, Error))))

where cell A4 contains text. If the text begins with "Q1", which means
Quarter 1, then I want it to return the value that is in cell U10. The
actual text shown in cell A4 is "Q1-86" for Quarter 1 1986.

Any help would be greatly appreciated.

Thanks,

Gary

The Error word need to be make text with quotes, otherwise XL thinks you are
referencing a cell call Error
Because you have not so named a cell, you get the #NAME! error

=IF(A4="Q1",U10,IF(A4="Q2",U11,IF(A4="Q3",U12,IF(A 4="Q4",U13, "Error"))))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
Hello,

I get a name error when I try to use this formula:

=IF(A4="Q1",U10,IF(A4="Q2",U11,IF(A4="Q3",U12,IF(A 4="Q4",U13, Error))))

where cell A4 contains text. If the text begins with "Q1", which means
Quarter 1, then I want it to return the value that is in cell U10. The
actual text shown in cell A4 is "Q1-86" for Quarter 1 1986.

Any help would be greatly appreciated.

Thanks,

Gary

A couple things:

1) You need quotation marks around the word Error in order for the
function
to return "Error" if it doesn't find any of the values

2) If you are only searching for part of a text value (i.e. "Q1"
instead of "Q1-86")
then you need to put the following statement in front of each "Q" in
order for it
to only find the first 2 characters of that string:

LEFT(A4,2)

The final formula should read:

=IF(LEFT(A4,2)="Q1",U10,IF(LEFT(A4,2)="Q2",U11,IF( LEFT(A4,2)="Q3",U12,IF(LEFT(A4,2)="Q4",U13,
"Error"))))


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=502920

Thanks! Your suggestion corrected the name error.

Now I can't seem to get XL to recognize the "Q1" from "Q1-86". It's
giving me "Error" for every result. I'm sure that I'm not using
wildcards correctly.

Thanks,

Gary

Thanks! The LEFT function corrected the second problem I was having.

Everything works perfectly now.

You need to use the LEFT function. See my post above


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=502920