How to select records meeting specific criteria?

Hi, I'm looking for help for an Excel problem. I want to select those records which meet specific criteria. To make this easy to explain, I attached an example here. Basically,

In cell B12, there is a dropdown list to select analyst code (AJ, AL, AM). The purpose is to select those companies that are located in the UK which is covered by an analyst. For example, if B12 is "AL", then companies of OPQ, UVW should be displayed.

Hi, arlu2101, just simply show the values will be fine. Just need to find these values.

Hi, DILIPandey, thanks for the really clever approach. Total enjoy it. Will learn and make it applicable to my case.

Originally Posted by dilipandey

HI BNCOXUK,

Try using below formula which need to be entered with key combination: ctrl shift enter
=IFERROR(INDEX(data,SMALL(IF(data_c="UK",ROW(data_c)-2,""),ROW($A1))),"")

where data and data_c are defined names which you can see by pressing key ctrl + F3

see attachment:-


Regards,
DILIPandey
<click on below * if this helps>

Thanks for your time to answer my question. It worked nicely