IF formula - too many levels of nesting

[hdan450]

Hi everyone,

I have been struggling to find an alternative to this for ages and have no idea what to do, any advice would be much appreciated.

I have an excel sheet, that has a list of people and they each select a team. The teams are home and away, and listed in cell ranges L4:L15 (home) and N4:N15 (away). I need to automatically populate the points, which are displayed in cell ranges K4:K15 (home) and O4:O15 (away).

The formula I have tried is at the bottom of the post, but it has too many levels of nesting. I basically want it to say something like this:

If value in cell B2, equals team in L4, display points shown in cell K4
Or...
If value in cell B2, equals team in L5, display points shown in cell K5
Or...
If value in cell B2, equals team in L6, display points shown in cell L6

this is for all the home teams. But the formula then carries on in case an away team has been chosen, ie:

If value in cell B2, equals team in N4, display points shown in cell O4
etc...

Here is the formula and thank you in advance if someone can help:

=IF(B2=$L$4,$K$4,(IF(B2=$L$5,$K$5,(IF(B2=$L$6,$K$6,(IF(B2=$L$7,$K$7,(IF(B2=$L$8,$K$8,(IF(B2=$L$9,$K$9,(IF(B2=$L$10,$K$10,(IF(B2=$L$11,$K$11,(IF(B2=$L$12,$K$12,(IF(B2=$L$13,$K$13,(IF(B2=$L$14,$K$14,(IF(B2=$L$15,$K$15,(IF(B2=$N$4,$O$4,(IF(B2=$N$5,$O$5,(IF(B2=$N$6,$O$6,(IF(B2=$N$7,$O$7,(IF(B2=$N$8,$O$8,(IF(B2=$N$9,$O$9,(IF(B2=$N$10,$O$10,(IF(B2=$N$11,$O$11,(IF(B2=$N$12,$O$12,(IF(B2=$N$13,$O$13,(IF(B2=$N$14,$O$14,(IF(B2=$N$15,$O$15)))))))))))))))))))))))))))))))))))))))))))))))

 

[hdan450]

Bump

 

[Jonmo1]

Try

=IFERROR(IFERROR(INDEX($K$4:$K$15,MATCH(B2,$L$4:$L$15,0)),INDEX($O$4:$O$15,MATCH(B2,$N$4:$N$15,0))),"No Match")

 

[Joe4]

I think it may be helpful for us to see what your data looks like to really get an idea of the data structure and what you are trying to do.
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Also, we should caution about bumping too soon, it could actually decrease your chances of getting a reply. There is a "Zero Reply Posts" listing that many of us use to look for unanswered questions. By replying to your own thread, you essentially remove it from that list. We generally advise people to wait 24 hours before bumping, as this is an international forum with people logging on at all hours all over the world.

 

[hdan450]

Try

=IFERROR(IFERROR(INDEX($K$4:$K$15,MATCH(B2,$L$4:$L$15,0)),INDEX($O$4:$O$15,MATCH(B2,$N$4:$N$15,0))),"No Match")

Hi mate

This is absolutely phenomenal, works perfectly, thank you so much

 

[hdan450]

I think it may be helpful for us to see what your data looks like to really get an idea of the data structure and what you are trying to do.
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

Also, we should caution about bumping too soon, it could actually decrease your chances of getting a reply. There is a "Zero Reply Posts" listing that many of us use to look for unanswered questions. By replying to your own thread, you essentially remove it from that list. We generally advise people to wait 24 hours before bumping, as this is an international forum with people logging on at all hours all over the world.

Hi Joe,

Thank you for looking at this, Jonmo1's formula works perfectly

Also, really sorry for bumping the thread, first time I have posted on here, I won't do it again

Thank you everyone

Have a nice day

Dan

 

[Jonmo1]

You're welcome.

It might be even better..
What type of values are in columns L and N, Number or Text?
Are they (or can they be) sorted in Ascending order?

 

[Joe4]

Thank you for looking at this, Jonmo1's formula works perfectly

I am glad he was able to figure it out. I guess I am just more of visual person. I thought I saw the pattern based on your first two example (L4 looks at K4 and L5 looks at K5), but then your last two threw me off (L6 looks at itself, and N4 looks at O4).

Also, really sorry for bumping the thread, first time I have posted on here, I won't do it again

No worries. It is not so much that you did anything wrong, I was just trying to impart some tips on how to have the best chance of getting views and responses. Many people worry that if there problem falls off the first page, there is little chance it will get answered. That isn't the case, as the "Zero Reply Posts" listing looks at ALL the unanswered threads.