Archive for Lookup and reference
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
DATEVALUE function not working
The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
Match a range value containing both text and numerical characters
Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array […]
Rearrange cells in a cell range to vertically distributed values
The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to extract numbers from a cell value
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
Identify all characters in a cell value
Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list […]
How to remove numbers from a cell value
The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]
The picture above shows the CHOOSE function in cell F3, one disadvantage is that you need to click each cell […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
INDEX and MATCH – multiple criteria and multiple results
The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied […]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
The array formula in cell D12 matches two values in two columns each and returns a value on the same […]
INDEX MATCH with multiple criteria
The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Filter values based on numerical ranges
Eero S asks: Thank you *so* much for your detailed examples and actively replying to users! I have a problem, […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
The formula in cell D3 lets you get the last value in column B, it works fine with blank cells […]
Find last matching value in an unsorted table
DonW asks: Ok, you've shown it for regular ranges....how about within tables. I have a table similar to: ID Name […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
Use VLOOKUP to calculate discount percentages
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]
Filter records that contain numeric values
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
Merge tables based on a condition
I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]
Find last matching value in an unsorted list
I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]
Prevent duplicate records [Data Validation]
Debra Dalgleish demonstrated last week how to block duplicate entries in excel table. I made a comment and I thought I would share […]
Quickly select a data set or an excel defined table [HYPERLINK]
If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, […]
Quickly jump to last row in a data set using excel hyperlink function
Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
Gets a value in a specific cell range based on a row and column number.
Drop down list changes cell formatting
You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is […]
VLOOKUP with multiple criteria
The VLOOKUP function cell D16 looks for both a value in column B and another value in column C. […]
Locate lookup values in a table [HYPERLINK]
Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]
Use Conditional Formatting to do lookups in related tables
It can be really hard trying to follow a lookup in related tables. I will show you how to use […]
How to use a Table name in Data Validation Lists and Conditional Formatting formulas
David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]
Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]
Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in […]
Calculate average of last 10 data with possible blank cells
Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]
Quickly concatenate values into one cell [No VBA]
Joining many many cell values in excel is not easy. The Concatenate function allows you to only reference a single […]
Compare two tables: Filter common records
I will in this blog post describe how to extract common records from two data sets in Excel. I have […]
Remove duplicate text strings based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Dynamic team generator in excel
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 […]
Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]
Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]
How to use absolute and relative references
What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]
Extract unique distinct year and months from dates
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
How to return a value if lookup value is in range
Question: Hi, What type of formula could be used if you weren't using a date range and your data was […]
VLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
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 […]
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.
Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.
Extract largest values from two columns
Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]
Search for a text string in a data set and return matching records
Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]
How to create a list of random unique numbers
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
How to perform a two-dimensional lookup
Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]
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 […]
Invert a list using cell references
Question: How do I invert / reverse a list using cell references? My list is in D3:D7, see picture below. […]
Create a unique distinct list and sort based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
How to quickly find the maximum or minimum value [Formula]
Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]
How to automatically fill all blanks with missing data or formula
Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]
5 easy ways to extract unique distinct values
You have quite a few options to choose from if you are looking for a way to create a unique […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Get cell addresses from several cells
Question: How do i get the cell adresses from the cells that contain TRUE? See picture below. Array formula in cell […]
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]
Problem: Chart dynamically updates source data depending on drop list selection. Solution: B4 is a drop list: Click tab "Data" […]
How to use the OFFSET function
In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
