Try this:

=IF(C2<"Not On List",1,VLOOKUP(IF(ISERROR(A2),B2,A2),'Ref
Tables'!$K$3:$L$12,2,FALSE))

--
Regards!
Stefi



€žLyons550€� ezt Ã*rta:

Hi All,

I'm trying to construct a formula that looks at the following section of data:

A B C D
#N/A less than 12mnths Not On List
12 to 18mnths #N/A 1

Essentially I want to show in Column D a value based on the following:
Where the value in column C shows anything other than "not On List" is
assigns "1"
If however, the value shown is Not On List then it needs to lookup the value
in Column A and return the corresponding value in the associated lookup table.

The catch comes when the value in Column A is #N/A...if that's the case it
should then look in Column B on the same basis as it looked in Column A.

Does any of that make sense...I hope so! I've tried the following...but have
got stuck.

=IF(ISNA(VLOOKUP(O5,'Ref Tables'!$K$3:$L$12,3,FALSE)),"1",VLOOKUP(M5,'Ref
Tables'!$K$3:$L$12,3,FALSE))