Excel Cell Value From List: Find or Select a Match

To return a value from a list when you know a matching item, use XLOOKUP; with product IDs in A2:A5, names in B2:B5, and the ID to find in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"Not found") to return the corresponding name. If you mean choosing a value from a drop-down list, use Data Validation instead—the selected item is stored directly in the cell.

What “cell value from list” can mean

The phrase can describe two related but different Excel tasks. A lookup searches a list for a known key, such as a product code, then returns information from another column. A drop-down list lets someone select an allowed value in a cell. Choosing between them matters: a lookup formula retrieves a result, while a drop-down controls what a user can enter.

For example, a price list might have product codes in one column and prices in another. If you type a code into an order sheet and want Excel to fill in its price, use a lookup formula. If you want staff to choose a department from a fixed set of approved names, use a drop-down list. You can also combine the methods: select a product code from a drop-down, then look up its description and price in adjacent cells.

Return one matching value with XLOOKUP

XLOOKUP is usually the clearest option in Microsoft 365 and Excel 2021 or later. It searches one range and returns the value from the corresponding position in another range. The lookup range and return range can be in different columns, and the return range can be either to the left or right of the lookup range.

With product IDs P-101 through P-104 in A2:A5, the corresponding product names in B2:B5, and P-103 in E2, enter this in F2:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found")

The result is Notebook, because P-103 is in the third position of the lookup range and Notebook is in the third position of the return range. XLOOKUP uses an exact match by default, which is appropriate for codes and names. The fourth argument supplies a readable result when the value is absent; without it, XLOOKUP returns an error for an unmatched item.

Keep the lookup and return ranges the same size and aligned to the same records. If A2:A5 contains the IDs but B3:B6 contains the names, the formula can return a value from the wrong row even though it calculates successfully. When copying the formula down a sheet, you may want to lock the source ranges with dollar signs so they do not shift.

Return every matching value

A lookup normally returns one result. If the same category can appear on several rows and you want all corresponding entries, FILTER can spill multiple results into nearby empty cells. FILTER is available in Microsoft 365 and Excel 2021 or later.

With categories in A2:A6, employee names in B2:B6, and the category to search for in E2, enter this formula in F2:

=FILTER(B2:B6,A2:A6=E2,"No matches")

If E2 contains Sales and the matching rows in B2:B6 are Ana and Dev, the formula returns both names in a vertical list. The cells below F2 need to be clear so the results can spill; otherwise, Excel displays a spill error. FILTER is useful when duplicates are meaningful, while XLOOKUP is a better fit when you expect one result per key.

Use INDEX and MATCH in older Excel versions

If XLOOKUP is unavailable in your Excel version, INDEX combined with MATCH can perform an exact lookup. These functions are supported in older desktop versions as well as current Excel. MATCH finds the position of the key, and INDEX returns the item at that position in the result range.

With product IDs in A2:A5, product names in B2:B5, and the ID to find in E2, enter:

=INDEX(B2:B5,MATCH(E2,A2:A5,0))

For E2 equal to P-103 and the example data above, the result is Notebook. The final 0 tells MATCH to find an exact match. If the key is missing, MATCH returns #N/A, which then makes the whole formula return #N/A. You can handle that with IFERROR if a friendly message is preferable.

VLOOKUP is another familiar option when the key is in the leftmost column of a table and the value to return is to its right. With IDs in A2:A5, names in B2:B5, and the search ID in E2, =VLOOKUP(E2,A2:B5,2,FALSE) returns Notebook for P-103. FALSE requests an exact match. Unlike XLOOKUP, VLOOKUP depends on a column number and cannot return a value from a column to the left of the lookup column.

Create a drop-down list in a cell

For a cell where a person should select an item rather than retrieve a related value, create a list validation rule. In Excel for Microsoft 365 on Windows:

Enter the allowed choices in a single column, for example A2:A6, with no blank rows between them.
Select the cell or range where users should make a selection.
Choose Data > Data Validation.
On the Settings tab, set Allow to List.
In Source, enter =$A$2:$A$6 if the list is on the same worksheet, and leave In-cell dropdown selected.
Select OK, then use the arrow in a validated cell to choose an item.

The chosen text becomes the cell’s value; a separate formula is not needed just to read that selection. For example, if a user selects Support in D2, another formula can refer to D2 as the selected department. To use a source list on another worksheet, define a name for the source range in Formulas > Define Name, then use that name as the Data Validation Source. This avoids relying on a direct cross-sheet range entry, which Data Validation may not accept as a source.

Excel for Mac also has Data Validation and list-based drop-downs, though the dialog layout can differ slightly. Excel for the web supports creating and using many validation lists, but some settings and management options are more limited than in the Windows desktop app. If the rule is complex or the list is on another sheet, the desktop app is a practical place to set it up.

Choose the method that fits the task

Use XLOOKUP when one key should return one related value and your Excel version includes the function.
Use INDEX and MATCH when you need a flexible lookup in an older Excel version.
Use VLOOKUP for a simple table where the lookup key is in the first column and the result is to its right.
Use FILTER when you want every row matching a condition, not just the first match.
Use Data Validation when a user should choose from permitted entries in a cell.

These approaches solve different problems, so there is no single best answer for every “value from list” question. A drop-down does not automatically return a price or description; it records the chosen item. A lookup does not prevent someone from typing an invalid key. Combining validation with a lookup can provide both controlled input and automatically retrieved details.

Common lookup and list problems

The formula returns #N/A or “Not found.” Check that the lookup value really exists and that the lookup and return ranges cover the intended rows. A number stored as text may not match the same-looking numeric value. Codes with leading zeros can also be affected if one side is stored as a number and the other as text. Make the data types consistent rather than changing the formula blindly.

The result is from the wrong record. Confirm that the lookup and return ranges start and end on corresponding rows. With VLOOKUP, check that the table range begins with the key column, the column index is correct, and the last argument is FALSE for an exact match. With XLOOKUP, verify that the return array points to the intended column.

The drop-down arrow or choices are missing. Select the cell and open Data > Data Validation to check that Allow is set to List and that the Source points to the right entries. A source range containing blanks may show blank choices. If the list changes often, keep its entries in a well-maintained range and update the validation source when necessary.

There are extra spaces or inconsistent spellings. Two entries that look alike may not be identical if one contains a trailing space or a different character. Clean the source data and standardize spelling and capitalization where appropriate. TRIM can remove ordinary extra spaces from text, but it does not fix every kind of hidden character, and cleaning only the search cell will not fix messy values in the lookup range.

Frequently Asked Questions

How do I get a value from a list in Excel?
Use XLOOKUP to search for a key and return its corresponding value. For example, search for a product ID and return the product name. If you mean letting someone choose an entry, create a Data Validation list instead.

How do I make a cell choose from a list?
Select the cell, choose Data > Data Validation, set Allow to List, and provide a source range or list. Keep In-cell dropdown selected so the cell displays a selection arrow.

Can Excel return more than one match?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER can return all rows that meet a condition. Leave enough empty cells below or beside the formula for the results to spill.

Why does my lookup show #N/A when the value is visible?
The displayed entries may differ underneath—for example, one may be text and the other a number, or one may contain an extra space. Check the underlying data type and clean both the lookup value and source list consistently.

Does a drop-down list fill in related cells automatically?
No. The drop-down stores the selected item in its cell. To fill a related description, price, or other field, put a lookup formula in that other cell and use the selected value as its lookup key.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.