Problem with IF function

[ash.gary]

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(A4="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

 

[Bernard Liengme]

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(A4="Q4",U13, "Error"))))

best wishes

 

[mphell0]

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"))))

 

[ash.gary]

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

 

[ash.gary]

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

Everything works perfectly now.

 

[mphell0]

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