Excel Toolkit / Function guides
XLOOKUP in Excel: explanation and examples
Find a site code, then return the corresponding value from another column. Start with one direct calculation.
These examples use the Moon Energy teaching case. All businesses and data are fictional. They are learning examples, not financial or tax advice.
Microsoft’s definition
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds.
Read the XLOOKUP function reference on Microsoft Support
Three ways to understand XLOOKUP
Transactional finance
Moon Energy’s finance team is reviewing opening-period supplier bill amounts and a comparison budget.
Return the actual for the chosen site code.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Site code | Site type | Bill net GBP | Budget | Selection / threshold |
| 2 | S01 | Hospital | £32,439.68 | £29,195.71 | S02 |
| 3 | S02 | Office | £7,420.00 | £6,678.00 | |
| 4 | S03 | Office | £6,128.42 | £5,515.58 | |
| 5 | S04 | Retail | £3,216.42 | £2,894.78 | |
| 6 | S05 | Retail | £3,771.49 | £3,394.34 |
Formula: =XLOOKUP(E2,A2:A6,C2:C6,"Not found")
Result for this sample: £7,420.00
Business operations
Moon Energy’s operations team is reviewing site electricity readings and a comparison consumption budget.
Return the actual for the chosen site code.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Site code | Site type | Electricity kWh | Budget | Selection / threshold |
| 2 | S01 | Hospital | 143695 | 129325.5 | S02 |
| 3 | S02 | Office | 32000 | 28800 | |
| 4 | S03 | Office | 26234 | 23610.6 | |
| 5 | S04 | Retail | 13234 | 11910.6 | |
| 6 | S05 | Retail | 15712 | 14140.8 |
Formula: =XLOOKUP(E2,A2:A6,C2:C6,"Not found")
Result for this sample: 32,000
Abstract
Moon Energy’s orbital stations hold photon-cell reserves. Their controller compares current reserves with planned reserves.
Return the actual for the chosen site code.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Station code | Station class | Photon cells | Budget | Selection / threshold |
| 2 | S01 | Hospital | 14500 | 13050 | S02 |
| 3 | S02 | Office | 3200 | 2880 | |
| 4 | S03 | Office | 2600 | 2340 | |
| 5 | S04 | Retail | 1300 | 1170 | |
| 6 | S05 | Retail | 1600 | 1440 |
Formula: =XLOOKUP(E2,A2:A6,C2:C6,"Not found")
Result for this sample: 3,200
Experiment in Formula Lab
Open XLOOKUP in the interactive Formula Lab →
The practice engine supports a focused subset of Excel behavior. Lookups are exact-match only; it does not support all Excel functions, dates, arrays or workbook imports. Read the limits shown in Formula Lab before interpreting a result.