Part A:
=IF(C1<"Not on list",1, VLOOKUP(A1,table,column,FALSE))
Part B
=IF(C1<"Not on list",1, IF(ISNA(VLOOKUP(A1,table,column,FALSE)),
VLOOKUP(B1,table,column,FALSE) ,VLOOKUP(A1,table,column,FALSE)) )
Neater formula if using Excel 2007
=IF(C1<"Not on list",1, IFERROR(VLOOKUP(A1,table,column,FALSE),
VLOOKUP(B1,table,column,FALSE) ))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP
"Lyons550" wrote in message
...
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))