## Archive for Lookup and reference

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 […]

Learn 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 […]

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 […]

Learn 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 on your […]

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

Table of Contents Overview Arguments Approximate match Horizontal and vertical lookup Horizontal and vertical lookup - INDEX + MATCH HLOOKUP […]

Everything you need to know about 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 […]

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Finds a value in a sorted cell range and returns a value on the same row.

INDIRECT(ref_text,a1) Arguments Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as […]

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 […]

ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. […]

How to use Excel’s MATCH function

Identify the position of a value in an array.

Fetch a value in a data set based on coordinates.

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 […]

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 […]

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 […]

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 refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

Create 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 […]

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 […]

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) […]

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. […]

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 […]

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

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 […]

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]