Dozens Of Possible Solutions, Which One Should I Use? Excel Nested If Condition With Vlookup

Excel function tutorials, Excel spreadsheets (.xls)

One of the challenges about resolving issues in excel is that there are usually so many different ways to resolve a given problem. It’s all about understanding the exact need and trying to find an optimal solution that will be:

-As easy as possible to create and modify later on
-Quick and efficient to use
-As light as possible
-etc

In this case, I received an email from a reader that had a challenge. I will simply show you a screenshot of the file that he sent and you will get the idea:

Interestingly, there are similarities between this problem and the golf scoring problem that I discussed recently. The main difference which makes it much more difficult is that the number of possible results is unlimited. That makes it challenging on many levels and makes it impossible to use a simple vlookup. I wasn’t quite sure how to tackle this specific problem. As is always the case, using a macro was certainly an interesting option. Using a nested if statement could have worked, or even a combination of a few of those but given the number of posssible outcomes, it would be very difficult to work with, and even more tricky to modify later on. Why?

Imagine using an If(xxx,y,if(xxx,y,if(xxx,……. Etc)

This would have gone on and on. My next reflex was to ask the reader if this would be used for multiple lines at a time. If so, that would make things even more difficult. Thankfully, in this specific case, it was going to be done one at a time. This meant that I could simply verfiy (with a nested if statement for each line if that specific answer was the correct one.

In O2 I added:

=IF(AND($F$4>M2,$F$4M2,$F$4

And you can download the spreadsheet of course:)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************