To enter a formula as an array formula in XLOOKUP in Excel, follow these steps:
Step 1: Open a new Excel worksheet and enter the data in the following table:
| A | B | C | |
|---|---|---|---|
| 1 | Fruit | Quantity | Price |
| 2 | Apples | 10 | $2.00 |
| 3 | Oranges | 15 | $1.50 |
| 4 | Bananas | 20 | $0.75 |
| 5 | Grapes | 25 | $3.00 |
Step 2: In cell F2, enter the following XLOOKUP formula:
=XLOOKUP("Oranges", A2:A5, C2:C5)Step 3: Instead of pressing Enter, press and hold the Ctrl+Shift+Enter keys on your keyboard to enter the formula as an array formula. The formula will be surrounded by curly braces to indicate that it is an array formula. The resulting formula in F2 should be:
{=XLOOKUP("Oranges", A2:A5, C2:C5)}Step 4: The result will be displayed in cell F2, which should show $1.50 as the price of oranges.
Step 5: To verify that the formula has been entered as an array formula, select the cell F2 and look at the formula bar. You should see the formula surrounded by curly braces as shown above.
That’s it! You have successfully entered a formula as an array formula in XLOOKUP in Excel.