if functions question
 

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


--
ulffersrc
------------------------------------------------------------------------
ulffersrc's Profile: http://www.excelforum.com/member.php...o&userid=26020
View this thread: http://www.excelforum.com/showthread...hreadid=393735

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=393735

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ulffersrc" wrote
in message ...

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


--
ulffersrc
------------------------------------------------------------------------
ulffersrc's Profile:
http://www.excelforum.com/member.php...o&userid=26020
View this thread: http://www.excelforum.com/showthread...hreadid=393735