# How to identify cell values based on 2 colums? Excel

Copper Contributor

# How to identify cell values based on 2 colums? Excel

I have a list of customer names in one column. names will be repeated. If I enter a specific name, all the cells corresponding to that name should be listed in another column.

Left part is question.

2 Replies

# Re: How to identify cell values based on 2 colums? Excel

``=IFERROR(INDEX(\$A\$2:\$A\$24,SMALL(IF(\$B\$2:\$B\$24=D\$1,ROW(\$B\$2:\$B\$24)-1),ROW(\$A1))),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell D2 and copied across range D2:E10.

An alternative could be the FILTER function if it's available in your version of Excel.

# Re: How to identify cell values based on 2 colums? Excel

As the other responder mentioned, if able to use FILTER this would be considerably more dynamic, especially if using table referencing. To make your data a table, you can use the shortcut Ctrl+T. An example to accomplish the filtering with a table would be:

``=FILTER(PartNums[PN],PartNums[Manufacturer]=E\$1,"")``

I named the table PartNums for ease of use, but make that whatever you'd like, and make your criteria of E\$1, F\$1, G\$1, etc. the cells where you are inputting your manufacturers' names.