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

Ask a Question

Top