4. Lookup second, third … nth occurrence of an item in a list in excel:
Often we work with data that has lot of duplicates, for eg. customer phone number data that has grown over a period of time with new numbers added at the bottom of the list. Getting second, third, fourth or nth occurrence from the list can be tricky, by using a combination of countif and vlookup we can lookup nth occurrence from a list. [VLOOKUP tutorial and examples ]
First in our data list we will insert another column and place the formula =current-item&countif(range till that point, item). this will append the number of smiths till that point to the end of smith, thus first smith would become smith1, second smith would become smith2, so on…
Next, when looking up smith instead of using the initial column of customer data we will use our modified customer data to fire the lookup, for eg. vlookup("smith3", lookup range, 2, false) would tell us the 3rd phone number of smith. Note the last argument to the vlookup as “false”, since our list may not be alphabetically sorted, we have to use “false” to force excel to keep looking till it finds
the 3rd smith row.
5. Reduce your nested if()s to one function
Do you know that you cannot next excel if functions beyond 7 levels? Thankfully, most of us never go beyond 3 or 4 levels. But why write even that many levels when you can use choose() function, which is like a switch structure in programming. A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3. ), for eg, =switch(3,"good","average","poor") would return “poor” when used. The only limitation to the switch() excel function is that it accepts numbers for testing the condition. But you can overcome this with some creativity, like I have shown in the below student letter grading example:
How did I convert the letter grade to a numeric in the choose(), well thats for you to figure out
What is your favorite IF formula trick?
Well, that is for you to tell me. What is your favorite Excel IF function tip / usage? Share it in the comments, let everyone know
Download IF Formula Examples and Play with them
I have prepared an excel sheet with all these if function examples, Click here to download it and experiment.