Finding up Duplicate Contain Texts Values from the Table & Display as Report

Finding up Duplicate Contain Texts Values in an Array


I have 3 columns, one is State, City & X Value as a Table (around 50000 records).

I need the solution for the below mentioned criteria.

Input Values: I can input the State in One Cell & City in another cell.
Table/Database Values: State, City & X Value (in %)

Once i give the state & city. I need the list of cities which contains the input value & the respective X value.

Objective is to find out When we input Los Angeles & California in 2 cells.

We should get the list of rows which contains Los Angeles in California City.

i.e. Los Angeles East, Los Angeles West, Lake Los Angeles

IT should display all the Cities name contains Los Angeles in California State.

For more clarity, i attached a sample file. it would be really useful when someone helps on how to get this report.

Note: This Result may lead to Maximum of 500 rows.


This Formula / Macro should do the work on we filtering the first row (heading) of the database -> selecting one state -> under City selecting Filter drop down -> Select Text Filter -> Select Contains .

Once we do this we will get the result which we need to derive it in a formula / in a Macro.

Thanks in Advance