Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to look up the last matching value in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=LOOKUP(2,1/(A2:A6=D2),B2:B6)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place your lookup value in a cell (e.g. D2).
- 2In the result cell, enter =LOOKUP(2,1/(A2:A6=D2),B2:B6).
- 3The expression A2:A6=D2 creates an array of TRUE/FALSE; 1/(…) converts TRUE to 1 and FALSE to #DIV/0!.
- 4LOOKUP ignores errors and scans to the last 1, effectively finding the last match.
Tips
- In Google Sheets, XLOOKUP with search_mode=-1 is cleaner: =XLOOKUP(D2,A2:A6,B2:B6,,,-1).
- The LOOKUP(2,1/(…)) trick is a classic that works in all Excel versions and Google Sheets without array entry.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does LOOKUP(2,...) work when there is no 2 in the array?
LOOKUP finds the largest value less than or equal to the lookup value. Since the array only contains 1s (and errors), LOOKUP returns the position of the last 1, which is the last match.
Does this work if the lookup column is unsorted?
Yes — the 1/(range=value) trick works on unsorted data because LOOKUP is searching a computed array, not the raw data.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.
How-toHow to look up a value across multiple columns
Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.