How to look up a value from a list typed into the formula
Formula (Excel and Google Sheets)
=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")Use array constants as the lookup and return ranges: =XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown").
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")Result"Active"
B2"A"The value in B2.
{"A","P","C","X"}{1 row × 4 cols} "A", "P", "C", "X"An array of values.
{"Active","Pending","Closed","Cancelled"}{1 row × 4 cols} "Active", "Pending", "Closed", "Cancelled"An array of values.
XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown")"Active"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
B2"A" - lookup_array
{"A","P","C","X"}{1 row × 4 cols} "A", "P", "C", "X" - return_array
{"Active","Pending","Closed","Cancelled"}{1 row × 4 cols} "Active", "Pending", "Closed", "Cancelled" - if_not_found
"Unknown"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(B2,{"A","P","C","X"},{"Active","Pending","Closed","Cancelled"},"Unknown"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFNA(VLOOKUP(B2,{"A","Active";"P","Pending";"C","Closed";"X","Cancelled"},2,FALSE),"Unknown") | Active, Closed, Pending… | 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") | Active, Closed, Pending… | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a value from a list typed into the formula
C2, turn the status code into its name (A Active, P Pending, C Closed, X Cancelled, anything else Unknown) without a lookup table on the sheet. Fill down to C7.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.