# Exceed 7 nested formula's (apparently)

J

#### Julie HSV

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

Ad

D

#### Dave Peterson

=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.

Ad

L

#### Luke M

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

## Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.