How to use INDEX/MATCH
✓ Verified in LibreOffice 25.8.7.3The portable power-lookup — works in every version, looks in any direction, survives column changes.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =INDEX(B2:B10,MATCH(D2,A2:A10,0)) | MATCH finds the row; INDEX returns the value in that row of the result column. The 0 = exact match. |
| Google Sheets | =INDEX(B2:B10,MATCH(D2,A2:A10,0)) | Identical. |
| LibreOffice Calc | =INDEX(B2:B10,MATCH(D2,A2:A10,0)) | Identical. |
How it works
Read it inside-out: MATCH(D2,A2:A4,0) finds WHICH row "banana" is in (row 2 of the range) using exact match (the 0), and INDEX(B2:B4, 2) returns the value in that row of the price column — 20. Because the lookup range (A) and return range (B) are named separately, INDEX/MATCH looks left as easily as right and doesn't break when you insert columns, unlike VLOOKUP's fixed column number. It's wordier than VLOOKUP but runs in every version of every app, which is why it stayed the power user's choice until XLOOKUP (which does the same thing in one call, on modern versions only).
Verified, not just documented
We ran =INDEX(B2:B4,MATCH(D2,A2:A4,0)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 20 — exactly the expected result. Every formula here is confirmed by actually executing it.