How To Lookup First And Last Match

Update: in the current version of Excel you can use the FILTER function to get all matches and the XLOOKUP function to get the last match only. Depending on your needs, FILTER might be a better option than first or last match.

One of the more confusing aspects of lookup functions in Excel is understanding how to get the first or last match in a set of data with more than one match. This is because Excel’s behavior changes depending (1) whether you are performing an exact or approximate match, and (2) whether data is sorted or not.

For example, if we use VLOOKUP to get the price for “green” in the data below, which price will we get?

Read on for the answer and more interesting examples.

Notes:

• The examples below use named ranges (as noted in the images) to keep formulas simple.
• Function reference links: VLOOKUP , INDEX , MATCH , and LOOKUP .

Exact match = first

When doing an exact match, you’ll always get the first match, period. It doesn’t matter if data is sorted or not. In the screen below, the lookup value in E5 is “red”. The VLOOKUP function , in exact match mode, returns the price for the first match:

=VLOOKUP(E5,data,2,FALSE)

Notice the last argument in VLOOKUP is FALSE to force exact match.

Approximate match = last

If you are doing an approximate match, and data is sorted by lookup value , you’ll get the last match. Why? Because during an approximate match Excel scans through values until a value larger than the lookup value is found, then it “steps back” to the previous value.

In the screen below, VLOOKUP is set to approximate match mode, and colors are sorted. VLOOKUP returns the price for the last “green”:

=VLOOKUP(E5,data,2,TRUE)

Notice the last argument in VLOOKUP is TRUE for approximate match.

Approximate match + unsorted data = danger

With standard approximate match lookups, data must be sorted by lookup value . With unsorted data, you may see normal-looking results that are totally incorrect. This problem is more likely with VLOOKUP because VLOOKUP defaults to approximate match when no fourth argument is provided.

To illustrate this problem, see the example below. Data is unsorted and VLOOKUP, with no fourth argument provided, defaults to approximate match. Notice there is no “red” with a price of $17.00, yet VLOOKUP happily returns this invalid result:

=VLOOKUP(E5,data,2)

For this reason, I recommend always setting the last argument for VLOOKUP explicitly: TRUE = approximate match, FALSE = exact match. The argument is optional, but providing a value makes you think about it, and provides a visual reminder in the future.

We’ll look at how to overcome the problem of last match and unsorted data below.

“Normal” approximate match

At this point, you may be feeling a little confused and disoriented about the idea that approximate match can return the last match in some cases. If so, don’t worry. Using approximate match to get the last match is not the “normal” case. Typically, you’ll see approximate match used to assign values according to some kind of scale. A classic example is using VLOOKUP in approximate match mode to assign grades, which works beautifully:

=VLOOKUP(E5,key,2,TRUE)

In cases like this, the lookup table deliberately does not include duplicate values, so the whole idea of “last matching value” is irrelevant. More details on this formula here .

The information above is to provide background and context for how matching works in Excel, so that the approaches described below make sense.

Practical applications

How can we use the behavior described above in a practical situation? Well, one common scenario is looking up the “latest” or “last” entry for an item. For example, below we are using VLOOKUP in approximate match mode to find the latest price for Sandals. Notice data is sorted by item, then by date, so the latest price for a given item appears last:

=VLOOKUP(F5,data,3,TRUE)

INDEX and MATCH

Other lookup functions can be used this way as well. Below, we are using an equivalent INDEX and MATCH formula find the latest price with the same data. Notice MATCH is configured to approximate match for items sorted in ascending order by setting the third argument to 1:

=INDEX(price,MATCH(F5,item,1))

LOOKUP function

The LOOKUP function can also be used in this case. LOOKUP always performs an approximate match, so it works well in “last match” scenarios. The formula is quite simple:

=LOOKUP(F5,item,price)

Last match with unsorted data

What if you want the last match, but data isn’t sorted by lookup value? In other words, you want to apply criteria to find a match, and you simply want the last item in the data that matches your criteria? This is actually a case where the LOOKUP function shines, because LOOKUP can handle array operations natively, without control + shift + enter. This means we can dynamically build a lookup array to locate the data we want using simple logical expressions.

For example, have a look at the formula below:

=LOOKUP(2,1/(item=F5),price)

This formula finds the latest price for Sandals in unsorted data.

You may not have seen a formula like this before, so let’s break it down in steps. Working from the inside out, we first apply the criteria with a simple logical expression:

item=F5

This results in an array of TRUE and FALSE values, where TRUE corresponds to items that are “sandals” and FALSE corresponds to all other values:

{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

Next, we divide the number 1 by this array. During division, TRUE becomes 1 and FALSE becomes zero, so you should visualize the operation like this:

1/{0;1;0;1;0;0;1;0}

One divided by one is one, and one divided by zero is #DIV/0, so the result is another array, this one containing only 1s and #DIV/0 errors:

{#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!}

Don’t worry, there is a method to this madness :)

Now, you may have noticed that the lookup value is the number 2. This may seem puzzling. How will LOOKUP ever find the number 2 in an array that contains only 1s and errors? It won’t. We are using 2 as a lookup value to force LOOKUP to scan to the end of the data .

The LOOKUP function will automatically ignore errors, so the only thing left to match are the 1s. It will scan through the 1s looking for a 2 that will never be found. When it reaches the end of the array, it will “step back” to the last valid value – the last 1 – which corresponds to the last match based on criteria provided.

INDEX and MATCH array version

The beauty of the LOOKUP function is it can handle the array operation described above natively in older versions of Excel without requiring you to enter as an array formula with control + shift + enter. However, you can certainly use an array formula if you like. Here is the equivalent INDEX and MATCH formula, which must be entered with control + shift + enter in older versions of Excel:

=INDEX(price,MATCH(2,1/(item=F5),1))

Note: in the current version of Excel , the above formula will just work without special handling. Also, the newer XMATCH function and XLOOKUP function can be directly configured to return the last match.

Last non-blank cell

The approach above turns out to be really useful. For example, by tweaking the logic a bit, we can do things like find the last non-empty cell in a column:

=LOOKUP(2,1/(B:B<>""),B:B)

This formula is described in more detail here .

Lookup nth match? All matches?

If you’ve made it this far, you may be wondering how you would find the second or third match, or how you would retrieve all matches? Here are some links for you:

• How to get nth match with VLOOKUP
• How to get nth match with INDEX and MATCH
• How to get all matches INDEX and MATCH

You’ll notice formulas like this get complicated. There are some cool new functions coming to Excel in 2019 that will make these solutions much simpler. Stay tuned. In the meantime, don’t forget that Pivot Tables are a great way to explore data without formulas .

What’s next?

• Formula basics - if you’re just getting started
• 500 formula examples with full explanations
• 101 important Excel functions
• Guide to all Excel functions (work in progress)
• Formula criteria - 50 examples
• Formulas for conditional formatting

Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel. More examples here .

What is a formula?

A formula in Excel is an expression that returns a specific result. For example:

=1+2 // returns 3

=6/3 // returns 2

Note: all formulas in Excel must begin with an equals sign (=).

Cell references

In the examples above, values are “hardcoded”. That means results won’t change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet.

Instead, use cell references so values can be changed at any time. In the screen below, C1 contains the following formula:

=A1+A2+A3 // returns 9

Notice because we are using cell references for A1, A2, and A3, these values can be changed at any time and C1 will still show an accurate result.

All formulas return a result

All formulas in Excel return a result, even when the result is an error. Below a formula is used to calculate percent change. The formula returns a correct result in D2 and D3, but returns a #DIV/0! error in D4, because B4 is empty:

There are different ways of handling errors. In this case, you could provide the missing value in B4, or “catch” the error with the IFERROR function and display a more friendly message (or nothing at all).

Copy and paste formulas

The beauty of cell references is that they automatically update when a formula is copied to a new location. This means you don’t need to enter the same basic formula again and again. In the screen below, the formula in E1 has been copied to the clipboard with Control + C:

Below: formula pasted to cell E2 with Control + V. Notice cell references have changed:

Below is the formula pasted to E3. Cell addresses are updated again:

Relative and absolute references

The cell references above are called relative references. This means the reference is relative to the cell it lives in. The formula in E1 above is:

=B1+C1+D1 // formula in E1

Literally, this means “cell 3 columns left “+ “cell 2 columns left” + “cell 1 column left”. That’s why, when the formula is copied down to cell E2, it continues to work in the same way.

Relative references are extremely useful, but there are times when you don’t want a cell reference to change. A cell reference that won’t change when copied is called an absolute reference . To make a reference absolute, use the dollar symbol ($):

=A1 // relative reference
=$A$1 // absolute reference

For example, in the screen below, we want to multiply each value in column D by 10, which is entered in A1. By using an absolute reference for A1, we “lock” that reference so it won’t change when the formula is copied to E2 and E3:

Here are the final formulas in E1, E2, and E3:

=D1*$A$1 // formula in E1
=D2*$A$1 // formula in E2
=D3*$A$1 // formula in E3

Notice the reference to D1 updates when the formula is copied, but the reference to A1 never changes. Now we can easily change the value in A1, and all three formulas recalculate. Below the value in A1 has changed from 10 to 12:

This simple example also shows why it doesn’t make sense to hardcode values into a formula. By storing the value in A1 in one place, and referring to A1 with an absolute reference , the value can be changed at any time and all associated formulas will update instantly.

Tip: you can toggle between relative and absolute syntax with the F4 key .

How to enter a formula

To enter a formula:

1. Select a cell
2. Enter an equals sign (=)
3. Type the formula, and press enter.

Instead of typing cell references, you can point and click, as seen below. Note references are color-coded:

All formulas in Excel must begin with an equals sign (=). No equals sign, no formula:

How to change a formula

To edit a formula, you have 3 options:

1. Select the cell, edit in the formula bar
2. Double-click the cell, edit directly
3. Select the cell, press F2 , and edit directly

No matter which option you use, press Enter to confirm changes when done. If you want to cancel, and leave the formula unchanged, click the Escape key.

Video: 20 tips for entering formulas

What is a function?

Working in Excel, you will hear the words “formula” and “function” used frequently, sometimes interchangeably. They are closely related, but not exactly the same. Technically, a formula is any expression that begins with an equals sign (=).

A function, on the other hand, is a formula with a special name and purpose. In most cases, functions have names that reflect their intended use. For example, you probably know the SUM function already, which returns the sum of given references:

=SUM(1,2,3) // returns 6
=SUM(A1:A3) // returns A1+A2+A3

The AVERAGE function , as you would expect, returns the average of given references:

=AVERAGE(1,2,3) // returns 2

The MIN and MAX functions return minimum and maximum values, respectively:

=MIN(1,2,3) // returns 1
=MAX(1,2,3) // returns 3

Excel contains hundreds of specific functions . To get started, see 101 Key Excel functions .

Function arguments

Most functions require inputs to return a result. These inputs are called “arguments”. A function’s arguments appear after the function name, inside parentheses, separated by commas. All have a name, and an opening and closing parentheses (). The inputs inside the parentheses are called “arguments”.The pattern looks like this:

=NAME(argument1,argument2,argument3)

For example, the COUNTIF function counts cells that meet criteria, and takes two arguments, range and criteria :

=COUNTIF(range,criteria) // two arguments

In the screen below, range is A1:A5 and criteria is “red”. The formula in C1 is:

=COUNTIF(A1:A5,"red") // returns 2

Video: How to use the COUNTIF function

Not all arguments are required. Arguments shown in square brackets [ ] are optional. For example, the YEARFRAC function returns the fractional number of years between a start date and an end date and takes 3 arguments:

=YEARFRAC(start_date,end_date,[basis])

Start_date and end_date are required arguments, basis is an optional argument. See below for an example of how to use YEARFRAC to calculate current age based on birthdate.

How to enter a function

If you know the name of the function, just start typing. Here are the steps:

1. Enter an equals sign (=) and start typing. Excel will make a list of matching functions based as you type:

When you see the function you want in the list, use the arrow keys to select (or just keep typing).

2. Type the Tab key to accept a function. Excel will complete the function:

3. Fill in required arguments:

4. Press Enter to confirm formula:

Combining functions (nesting)

Many Excel formulas use more than one function, and functions can be " nested " inside each other. For example, below we have a birthdate in B1 and we want to calculate the current age in B2:

The YEARFRAC function will calculate years with a start date and end date:

We can use B1 for the start date, and then use the TODAY function to supply the end date:

When we press Enter to confirm, we get current age based on today’s date:

=YEARFRAC(B1,TODAY())

Notice we are using the TODAY function to feed an end date to the YEARFRAC function. In other words, the TODAY function can be nested inside the YEARFRAC function to provide the end date argument. We can take the formula one step further and use the INT function to chop off the decimal value:

=INT(YEARFRAC(B1,TODAY()))

Here, the original YEARFRAC formula returns 20.4 to the INT function, and the INT function returns a final result of 20.

Notes:

1. The screens above were created on February 22, 2019.
2. Nested IF functions are a classic example of nesting functions .
3. The TODAY function is a rare Excel function with no required arguments.

Key takeaway: The output of any formula or function can be fed directly into another formula or function.

Math Operators

The table below shows the standard math operators available in Excel:

Logical operators

Logical operators provide support for comparisons such as “greater than”, “less than”, etc. The logical operators available in Excel are shown in the table below:

Video: How to build logical formulas

Order of operations

When solving a formula, Excel follows a sequence called “order of operations”. First, any expressions in parentheses are evaluated. Next Excel will solve for any exponents. After exponents, Excel will perform multiplication and division, then addition and subtraction. If the formula involves concatenation , this will happen after standard math operations. Finally, Excel will evaluate logical operators , if present.

1. Parentheses
2. Exponents
3. Multiplication and Division
4. Addition and Subtraction
5. Concatenation
6. Logical operators

Tip: you can use the Evaluate feature to watch Excel solve formulas step-by-step.

Convert formulas to values

Sometimes you want to get rid of formulas and leave only values in their place. The easiest way to do this in Excel is to copy the formula, then paste, using Paste Special > Values. This overwrites the formulas with the values they return. You can use a keyboard shortcut for pasting values, or use the Paste menu on the Home tab on the ribbon.

Video: Paste Special Shortcuts

What’s next?

• 29 tips for working with formulas and functions ( video version here )
• 500 formula examples with full explanations
• 101 important Excel functions
• Guide to all Excel functions (work in progress)
• Excel formula errors (examples and fixes)
• Formula criteria - 50 examples
• Formulas for conditional formatting
• How to use F9 to debug a formula (video)
• Excel formula errors and fixes (video)