The formula
ExcelExcel
=INDEX(B2:B4,MATCH(TRUE,EXACT(A2:A4,B6),0))In E2 → 10%
Excel 365Excel
=XLOOKUP(TRUE,EXACT(A2:A4,B6),B2:B4)In E3 → 10%
Google SheetsGoogle Sheets
=ARRAYFORMULA(INDEX(B2:B4,MATCH(TRUE,EXACT(A2:A4,B6),0)))In E4 → 10%
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Discount | |||
| 2 | save10 | 5% | 10% | ||
| 3 | SAVE10 | 10% | 10% | ||
| 4 | Save10 | 15% | 10% | ||
| 5 | |||||
| 6 | Code | SAVE10 |
How it works
- EXACT(A2:A4,B6)
- TRUE only where the code matches with the same upper/lower case.
- MATCH(TRUE,…,0)
- Position of the first TRUE.
- INDEX(B2:B4,…)
- Returns the discount at that position.
Common errors and fixes
Why does the formula return #N/A?
No exact-case match. Remember EXACT also treats extra spaces as different.
Always returns the first row?
In Excel 2019 or older, enter with Ctrl+Shift+Enter.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.