0

I have a spreadsheet where I want a contact name to appear in a cell F166 if the department number P166 appears in a named range (PS Depts on Sheet2).

Here is my existing formula, but it only allows for lookup of one dept. I don't want to have to write an if statement for each of my 112 depts in the named range. The section of the formula that applies to my dilema is marked bold italic.

=IF((LEFT(P166,1)="2"),$P166,(IF((LEFT(S166,5)="BU344"),"get from finance Heather",***(IF((LEFT(P166,6)="449010"),"get from Finance Angela H"***,"get from finance Shawn")))))

Translation :

  • If the cell value starts with 2, get from Heather.
  • If the cell value appears in the named range, get from Angela.
  • If neither, get from Shawn.

The formula works quite well as is. I just want to reference the new named range in order to identify which rows should name Angela H in the cell.

Improve this question

1 Answer 1

Reset to default
1

Try using COUNTIF to check a range, e.g. this version

=IF(LEFT(P166,1)="2",$P166,IF(LEFT(S166,5)="BU344","get from finance Heather",IF(COUNTIF(PSDepts,LEFT(P166,6)),"get from Finance Angela H","get from finance Shawn")))

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .