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))

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))

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))