How to identify cell values based on 2 colums? Excel

Copper Contributor

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.

right part is answerCapture.PNG

2 Replies



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.

PN Manufacturer.png

An alternative could be the FILTER function if it's available in your version of 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:




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.