Index/Match Formulas - Search/Filter Based on Specific Criteria

**Christopher.Belanger** · 08-09-2012, 07:00 AM

I work on a daily basis with mass data dumps from several different types of data collection systems. I am essentially a data analyst and I present this large sums of data in a clean cut and organized matter based on several different criteria. Most of this organization can be done by filtering or using sumifs/ifs/vlookups/hlookups. Although, the extent of the data collection and organization has come to its extents.

I am begining to dig into the Index/Match Formulas where I can essentially take large amounts of data and reduce them in size on additional sheets.

What I am asking is for a solid learning platform on how to use the index/match functions.

I would like to be able to take data from one sheet and filter it down to a much smaller size based on specific criteria.

Any help/tools would be very useful

Thanks

**Pepe Le Mokko** · 08-09-2012, 07:19 AM

Nice link at http://www.excelhero.com/blog/2011/0...ing-index.html

**The_Snook** · 08-09-2012, 08:07 AM

I find Chandoo's website an excellent Excel resource (www.chandoo.org)

This is a link to an INDEX / MATCH article on the site (http://chandoo.org/wp/2010/11/02/how...alues-to-left/)

**Christopher.Belanger** · 08-09-2012, 03:56 PM

What about examples on multiple criteria? I'm almost there.

I appreciate the help

**Apelcius** · 08-09-2012, 04:21 PM

I'm really good with the Index/match stuff, but i don't really have a clear grasp of what you're looking for. perhaps give a general table and explain what you're trying to get out of it?
even if you have to explain the multiple criteria in formula form, i'll be able to look into it.

**Christopher.Belanger** · 08-10-2012, 08:42 AM

Please see the attached sample file.

So for example - Say that the columns that are peach in color or columns B - E are my filters and I only want to select rows that have the following:

223, Structural Steel, OS and I only want to return the cost codes with these specified criteria

As in I want to take information from this "Sample" set based on criteria as listed above and I want to return all associated values within that criteria on another sheet.

Essentially what i'm doing is filtering data and placing it on another page. I'm trying to automate several reports that i need to generate on a monthly basis.

These reports are specific to criteria as listed above.

The unique identifier to each row is the "cost code" column. Costs are associated with each code. These codes have been classified (columns B - E) and the period and JTD (Job to Date) data has been compiled in columns G - N.

I hope this makes sense.

I am on the brink of automating all of my reports. This will cut out several days of work if I can begin to index properly. I just need a helping hand.

All help is greatly appreciated.