How to extract data from a table based on multiple criteria

[gwstudent]

Hi -

I have posted a sample file here: https://docs.google.com/file/d/0B2V4Nkca2ASFUkV5TVBxSzlLaVU/edit?usp=sharing


Suppose I have 3 columns of data.


ColA = City

ColB = State (50 U.S. States)

ColC = Name


How would I get ALL of the names, from ColC, based on required criteria of B and C? For example, City = Honolulu and State = Hawaii, show me ALL of the names in ColD if these two criteria are true.


I can't quite get this to work. Here is what I have come up with.


=INDEX(A2:C7,MATCH(0,COUNTIF($E$2:$E2,$A$2:$A$7)+($B$2:$B$7<>"HI"),0),COLUMN(C2))

 

[inbp]

hi gwstudent


hope you are fine


I have a solution for you if you have excel 2010 or 2013


i have used slicer in pivot table as filter


in older version you can use filter option in pivot. i did both in below file


https://www.dropbox.com/s/9wnc56mmqoc82ms/unique-distinct-list-matching-clean%20gwstudent.xlsx


i would be happy to help you, if you have any misunderstanding regarding this.

 

[gwstudent]

That's a nice option. Can you think of a programmatic method?

 

[inbp]

what do you mean programmatic method?

 

[gwstudent]

A formula - not VBA. Essentially, I am looking hoping to just have a page that has all the data without the need to filter anything. I intend to create a page that has divisions by state.

 

[Proficient]

May be you want something like this...


https://www.dropbox.com/s/bsrlj19i4wybc8r/Unique%20Distinct.xlsx


Regards,

Kamran Mumtaz

 

[Hui]

GWStudent

Have a read of a Formula based solution here: http://chandoo.org/wp/2011/11/18/formula-forensics-003/


FYI - Programmatic generally refers to the use of a Programming language ie: VBA not a formula based solution