named range formula

[Jack in the UK]

=IF(OR(OFFSET(AF5,0,-4,1,1)={"Jack"}),Notes!B1,Notes!B2)

need to replace jack with name range

 

[Jay Petrulis]

On 2002-05-07 07:51, Jack in the UK wrote:
=IF(OR(OFFSET(AF5,0,-4,1,1)={"Jack"}),Notes!B1,Notes!B2)

need to replace jack with name range

Switch to the MATCH function with the named range. I named a range 'Jack'

=IF(ISNA(MATCH(OFFSET(AF5,0,-4,1,1),Jack,0)),Notes!B2,Notes!B1)

You might also want to use SEARCH or FIND if your named range is a field of strings (not a cell range).

HTH,
Jay