Excel University Blog

Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.

VLOOKUP

VLOOKUP Hack #8: Extra Spaces

By Jeff Lenning | December 12, 2017 |

Here’s a VLOOKUP question for you: What happens when the lookup value includes extra spaces? For example, a few trailing spaces, or leading spaces, or, extra spaces in the middle of the text string. When the lookup value has extra spaces, but the lookup range values don’t, VLOOKUP runs into problems. This leads us to…

Read More

VLOOKUP Hack #7: Different Columns

By Jeff Lenning | December 6, 2017 |

In this post, we’ll continue hacking the 1st argument. Here is the basic setup. We are building a report. The report structure uses multiple columns to store the report labels, such as sales, cost of sales, selling expenses, and so on. So, can we write a formula that works when the lookup values are stored…

Read More

VLOOKUP Hack #6: Different Data Types

By Jeff Lenning | November 29, 2017 |

We started this blog series by examining the 4th argument. We’ve since hacked the 3rd argument and then the 2nd argument. Now, as you can imagine, it is time to hack the 1st argument. There are many fun hacks we can do with the 1st argument, so, I’ll cover them over a few posts. In…

Read More

VLOOKUP Hack #5: Different Tables

By Jeff Lenning | November 15, 2017 |

We are right in the middle of a blog series called VLOOKUP Hacks. We started by exploring the 4th VLOOKUP argument. Then we hacked the 3rd argument. Now, as you may have imagined, it is time to hack the 2rd argument. In this post, we’ll allow the user to retrieve values from different lookup tables.…

Read More

VLOOKUP Hack #4: Column Labels

By Jeff Lenning | November 8, 2017 |

This is the 4th post in the VLOOKUP Hacks series. The first three posts have explored the 4th argument. Now, we are going to explore a hack for the 3rd argument. In this post, we’ll hack the 3rd argument so that it references column labels instead of the column position. Check it. Note: depending on…

Read More

VLOOKUP Hack #3: Exact Match

By Jeff Lenning | November 1, 2017 |

In this post, the 3rd in the VLOOKUP Hacks series, we’ll dig even deeper into the 4th argument. We’ll see how we can leverage it to help with our reconciliations and other list comparisons. Let’s pretend for a moment that we need to compare two lists. We’d like to find out which items in one…

Read More

VLOOKUP Hack #2: Range Lookups

By Jeff Lenning | October 25, 2017 |

In the first VLOOKUP Hacks post, we talked about how the 4th argument impacts the sort order. But, there is more to uncover about this 4th argument. So, let’s pick up right where we left off. So far, we understand that the 4th argument tells Excel whether we are looking for a value between a…

Read More

VLOOKUP Hack #1: Sort Order

By Jeff Lenning | October 18, 2017 |

VLOOKUP is one of the most popular Excel functions. It can do amazing things and help save time. But, there are some really cool hacks that make it even better. This is the first post in a series of VLOOKUP Hacks. Hope you enjoy 🙂 Issue VLOOKUP Hack #1 helps address the sort issue. Sort…

Read More

VLOOKUP vs INDEX-MATCH

By Jeff Lenning | May 25, 2017 |

There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP…

Read More

Get & Transform: An Alternative to VLOOKUP List Comparisons

By Jeff Lenning | August 18, 2016 |

I recently received an Excel question about how to perform a specific list comparison, and I thought I’d demonstrate how to use a Get & Transform query as an alternative to the formula-based list comparisons typically performed with functions such as VLOOKUP and COUNTIFS. The original question is: “I am trying to use a list…

Read More