Exceed 7 nested formula's (apparently)
 

I'm creating a formula which when there is an "X" in D8, it looks at B5 which
is the year and if that year matched with a heading in Row 6, take the
relevant price, if no match leave blank.

I need to match 8 years (2010 - 2017) and the formula will not allow me to
match more than 3, does anyone have a suggestion of how I can change my
formula to include all years ?

=IF((D8="x"),IF($B$5=$J$6,J8,IF($B$5=$K$6,K8,IF($B $5=$L$6,L8)))," ")

Thanks so much

Exceed 7 nested formula's (apparently)
 

=IF(D8<"x","",IF(ISNA(MATCH($B$5,$J$6:$Q$6,0)),"" ,
INDEX($J8:$Q8,MATCH($B$5,$J$6:$Q$6,0))))

If it's not an X, then show "".
If there is not match between the date (B5) and J6:Q6, then show "".

Otherwise, retrieve the value under the matching year.



Julie HSV wrote:

I'm creating a formula which when there is an "X" in D8, it looks at B5 which
is the year and if that year matched with a heading in Row 6, take the
relevant price, if no match leave blank.

I need to match 8 years (2010 - 2017) and the formula will not allow me to
match more than 3, does anyone have a suggestion of how I can change my
formula to include all years ?

=IF((D8="x"),IF($B$5=$J$6,J8,IF($B$5=$K$6,K8,IF($B $5=$L$6,L8)))," ")

Thanks so much

--

Dave Peterson

Exceed 7 nested formula's (apparently)
 

It looks like you can do this:

=IF(D8="x",LOOKUP($B$5,$J$6:$Q$6,$J8:$Q8),"")

If there's a possibility of D8= x and B5 is not found in B5:J6, you can do
this:
=IF(AND(D8="x",ISNUMBER(MATCH($B$5,$J$6:$Q$6,0))), LOOKUP($B$5,$J$6:$Q$6,$J8:$Q8),"")

--
Best Regards,

Luke M
"Julie HSV" wrote in message
...
I'm creating a formula which when there is an "X" in D8, it looks at B5
which
is the year and if that year matched with a heading in Row 6, take the
relevant price, if no match leave blank.

I need to match 8 years (2010 - 2017) and the formula will not allow me to
match more than 3, does anyone have a suggestion of how I can change my
formula to include all years ?

=IF((D8="x"),IF($B$5=$J$6,J8,IF($B$5=$K$6,K8,IF($B $5=$L$6,L8)))," ")

Thanks so much