Data is unsorted and VLOOKUP, with no fourth argument provided, defaults to approximate match. To illustrate this problem, see the example below. This problem is more likely with VLOOKUP because VLOOKUP defaults to approximate match when no fourth argument is provided. With unsorted data, you may see normal-looking results that are totally incorrect. With standard approximate match lookups, data must be sorted by lookup value. Approximate match + unsorted data = danger Notice the last argument in VLOOKUP is TRUE for approximate match. VLOOKUP returns the price for the last 'green': =VLOOKUP(E5,data,2,TRUE) In the screen below, VLOOKUP is set to approximate match mode, and colors are sorted. 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.

If you are doing an approximate match, and data is sorted by lookup value, you'll get the last match.

Notice the last argument in VLOOKUP is FALSE to force exact match. The VLOOKUP function, in exact match mode, returns the price for the first match: =VLOOKUP(E5,data,2,FALSE) In the screen below, the lookup value in E5 is 'red'. It doesn't matter if data is sorted or not. When doing an exact match, you'll always get the first match, period.

Function reference links: VLOOKUP, INDEX, MATCH, and LOOKUP.

The examples below use named ranges (as noted in the images) to keep formulas simple.

Read on for the answer and more interesting examples. 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.įor example, if we use VLOOKUP to get the price for 'green' in the data below, which price will we get? 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. Depending on your needs, FILTER might be a better option than first or 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.

Index match