# How to look up a value from a list typed into the formula

Use array constants as the lookup and return ranges: `=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")`.

**Excel:** `=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Order | Status code | Status |
| SO-7101 | A |  |
| SO-7102 | C |  |
| SO-7103 | P |  |
| SO-7104 | X |  |
| SO-7105 | A |  |
| SO-7106 | Q |  |

Formula in C2, filled to C7: `=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")`

Result: Active; Closed; Pending; Cancelled; Active; Unknown

## How it works

Braces make a list inside the formula: `{"A","P","C","X"}` is a one-row array of codes, and the second array holds the names in the same order. XLOOKUP treats them like ranges, finds the code and returns the name in the same position.

## Other ways

- `=IFNA(VLOOKUP(B2,{"A","Active";"P","Pending";"C","Closed";"X","Cancelled"},2,FALSE),"Unknown")`: A two-column table in braces: commas separate columns and semicolons separate rows. Works in every version.
- `=SWITCH(B2,"A","Active","P","Pending","C","Closed","X","Cancelled","Unknown")`: SWITCH lists each code next to its result, which is easier to read for a few codes. Excel 2019 and later.

## Common mistakes

- Both arrays must have the same number of items, in the same order.
- In some regional settings the separators inside braces are different (for example `;` and `\`). Excel shows an error if they don't match your locale.
- If the list changes often or is long, keep it in cells instead so it can be edited without touching formulas.

## Questions

**Can I use a list of numbers?**

Yes: `=XLOOKUP(B2,{1,2,3},{"Low","Medium","High"})`.

**Does this work in Google Sheets?**

Yes, with the same braces, commas and semicolons in US settings.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-from-array-constant