I need to find specific a value across a row using IF(AND

  • Thread starter Thread starter SCrowley
  • Start date Start date
S

SCrowley

I have searched through the archives and threads and thought I found the
solution but to no success.

I want to find "DC Office" across cells AW2:BK2, if one of those cells
contains that text I need it to populate BL2 with "Washington, DC"

Here is the formula I've tried:
=IF(AW2="DC Office","Washington, DC",IF(AND(AX1="DC Office"),"Washington,
DC",IF(AND(AY1="DC Office"),"Washington, DC",IF(AND(AZ1="DC
Office"),"Washington, DC",))

Any help is greatly appreciated. Thank you.
 
What should it return if none of the cells contain your criteria? In this
formula it will return blank in that case.

=IF(COUNTIF(AW2:BK2,"DC Office"),"Washington DC","")

HTH,
Paul
 
WORKS BEAUTIFULLY! Thank you.
--
Thank you,

scrowley(AT)littleonline.com


PCLIVE said:
What should it return if none of the cells contain your criteria? In this
formula it will return blank in that case.

=IF(COUNTIF(AW2:BK2,"DC Office"),"Washington DC","")

HTH,
Paul

--
Click to expand...
 
Can I look for more than one string?
=IF(COUNTIF(AW2:BK2,"DC Office","Charlotte Office","Dallas
Office"),"Washington DC","Charlotte, NC","Dallas, TX","")


--
Thank you,

scrowley(AT)littleonline.com


PCLIVE said:
What should it return if none of the cells contain your criteria? In this
formula it will return blank in that case.

=IF(COUNTIF(AW2:BK2,"DC Office"),"Washington DC","")

HTH,
Paul

--
Click to expand...
 
Thank you so much. Is it possible to look for more than one string?
=IF(COUNTIF(AW2:BK2,"DC Office","Charlotte Office","Dallas
Office"),"Washington DC","Charlotte, NC","Dallas, TX","")

--
Thank you,

scrowley(AT)littleonline.com


PCLIVE said:
What should it return if none of the cells contain your criteria? In this
formula it will return blank in that case.

=IF(COUNTIF(AW2:BK2,"DC Office"),"Washington DC","")

HTH,
Paul

--
Click to expand...
 
There is probably another way to do it, but you could use a nested IF
statement here.

=IF(COUNTIF(AW2:BK2,"DC Office"),"Washington
DC",IF(COUNTIF(AW2:BK2,"Charlotte Office"),"Charoltte,
NC",IF(COUNTIF(AW2:BK2,"Dallas Office"),"Dallas, TX","")))

HTH,
Paul

--

SCrowley said:
Thank you so much. Is it possible to look for more than one string?
=IF(COUNTIF(AW2:BK2,"DC Office","Charlotte Office","Dallas
Office"),"Washington DC","Charlotte, NC","Dallas, TX","")
Click to expand...
 
Back
Top