How to identify cell values based on 2 colums? Excel

[Nandha_Kumar_LK]

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 answer

[#2]

@Nandha_Kumar_LK 

=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.

[#3]

@Nandha_Kumar_LK 

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.