if functions question

U

ulffersrc

I have the following formula:

=IF(B2:F2="ru", "Richard", IF(B2:F2="ma", "Martin", IF(B2:F2="bd",
"Benita", IF(B2:F2="la", "Leslie", ""))))


The problem is the range is not recognized. The formula only works for
one column at a time because I want the result to happen below the
range, say in column F, not at the end or with an extra column (like G
or H).

Do I need to use some COUNTIF idea? or something else?

Thanks,
rcu
 
D

duane

this will work, but there is a pecking order so if both bd and ma exists
in the b2:f2 range, Richard will be the result, and so on.

=IF(COUNTIF(B2:F2,"ru")>0, "Richard", IF(COUNTIF(B2:F2,"ma")>0,
"Martin", IF(COUNTIF(B2:F2,"bd")>0, "Benita", IF(COUNTIF(B2:F2,"la")>0,
"Leslie", ""))))
 
M

Max

Another play to try ..

Assuming each data row (i.e. B2:F2, B3:F3, B4:F4, etc)
will contain, at the most, only one match* for the 4 initials

Put in say, G2, and array-enter (press CTRL+SHIFT+ENTER):

=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH({"ru";"ma";"bd";"la"},$B2:$F2,0)),0)),"",
INDEX({"Richard";"Martin";"Benita";"Leslie"},MATCH(TRUE,ISNUMBER(MATCH({"ru"
;"ma";"bd";"la"},$B2:$F2,0)),0)))

Copy G2 down to return correspondingly for B3:F3, B4:F4, etc

Rows w/o any matches will return blanks: ""

*If there's more than one match,
only the first match (from the left) will be returned
 
Top