Vlookup a cell range and return multiple values
VLOOKUP a multi-column range and return multiple values.
Latest modified blog articles
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
How to save custom functions and macros to an Add-In
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
7 days (weekly) date ranges using a formula
Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]
The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]
The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Filter unique distinct records with a condition
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Calculate running average of last 10 data with random blank cells
Question: List of data and blank cells in a column which will be added from day to day. There are […]
Compare your stock portfolio with S&P500 in Excel
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
Filter unique distinct records (case sensitive) [UDF]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
Filter unique distinct values (case sensitive) [UDF]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Combine cell ranges eliminating blanks
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
Running totals based on criteria
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Dependent drop down lists – Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
How to highlight differences in price lists
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Filter records based on a date range and a text string
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Text to columns: Split words in a cell [Array formula]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Highlight records containing text strings (AND Logic)
The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]
Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Highlight records – multiple criteria [OR logic]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Search for a text string in a data set using an array formula
Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]
How to customize the plot area
The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]
What's on this page Chart area Chart title Legend Axis title Axis lines/values Tick marks Plot area Chart series […]
Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Find positive and negative amounts that net to zero [UDF]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Count digits and ignore duplicates
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Create links to all sheets in a workbook
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
I have created another monthly calendar template for you to download. Select a month and year in cells A1 and […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
Highlight unique distinct records
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]
Question: I am trying to create an excel spreadsheet that has a date range.Example: Cell A1 1/4/2009-1/10/2009 Cell B1 1/11/2009-1/17/2009 […]
Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Compare two tables: Remove common records
Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
Count cells based on a condition and month
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Use text qualifiers to make text to columns conversion easier [VBA]
This blog post describes how to insert qualifers to make "text to columns" conversion easier. Example I copied a table from […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the […]
How to use the AMORLINC function
The AMORLINC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. Formula […]
Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Filter not shared values out of two cell ranges [UDF]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
Filter values in common between two cell ranges [UDF]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
Filter duplicates in a large dataset [UDF]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
Sort based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
Compare two columns and return differences
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Count unique distinct values in a large dataset [UDF]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Extract unique distinct values based on a date range
Question: I have a large list of dates and other adjacent values. I want to create a distinct list from […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]
Sort records based on two columns
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
Search two related tables simultaneously [VBA]
Let's say you do a lot of searches in two tables. The tables are related so it would be great […]
The user defined function demonstrated in the animated ggif below, resizes a range you specify to columns or rows you also […]
Joe asks: Hello Oscar,I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate additional […]
A quicker A * pathfinding algorithm
3 weeks ago I showed you a A* pathfinding algorithm. It was extremely slow and sluggish and I have now made it […]
Lookup with an unknown number of criteria
Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem […]
Vlookup with multiple matches returns a different value
Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
Split words in a cell range into a cell each [UDF]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
Welcome! I am 