Blog

Select Drop Down Item and use VLOOKUP to Return Multiple Attributes

In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our […]


Date Data Validation Drop-Down

In this post, we’ll create two data validation drop-down cells that provide the ability to select From and To dates based on the transaction dates stored in the source data. This will ensure that the date selections fall within a valid range of dates, that is, those months with data in the table. This technique […]


Unique Data Validation Drop-Down From Duplicate Table Data

In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values. Objective Before we get started on the mechanics, let’s take a look at what we are trying to achieve. We have a data table that is exported from our […]



Invitation to the Excel Modeling World Championships 2014

Hi all! We have been formally invited to participate in the upcoming Excel modeling championship. Wow…a world-wide Excel competition…awesome! The invitation from John Persico, one of the lead organizers of the ModelOff event, is below…check it out! Invitation to the Excel Modeling World Championships 2014 We’d like to invite members of the Excel University community […]


SUMIFS with OR

Of all the functions introduced in Excel 2007, 2010, and 2013, my personal favorite is SUMIFS. The SUMIFS function performs multiple condition summing. The function is designed with AND logic, but, there are several techniques that allow us to use OR logic instead. This post explores a few of them. Objective Let’s be clear about […]



VLOOKUP and Wildcards

In a previous post, we explored how to use the TRIM function to remove trailing spaces from lookup values used in a VLOOKUP function. In this post, we’ll tackle the reverse issue, where the values in the lookup column contain trailing spaces, by using wildcards in the VLOOKUP function. Objective Before we get started, let’s […]


List Comparisons CalCPA Magazine Article

Hi guys! Just wanted to draw your attention to a recent California CPA Magazine article I wrote about comparing lists using Excel. Have you ever wanted to ask Excel to identify which items from one list appear on another? For example, which check numbers on the check register appear on the bank download, as illustrated […]