Index Match help with certain criteria.

**Vasool** · 04-29-2013, 12:27 AM

Hello, this forum is a wonderful resource, and I have been looking at it for a few weeks now ever since I started to use excel.

I created my first experiment with excel, a sheet for my boss, with all sort of tables and information my boss requires. What I need help with is an index match formula.

If you look at cell AG:7 in table called TableSTATS2, it is giving me a result that is somebody's name. It is supposed to give me one of the five store numbers instead. (504 or 696 or 1506 or 2351 or 2644).

I'm having the same problem with cell AG:10, and I realize the reason this is happening is because it is matching the first occurrence of the match, and I only need it to match the number from the totals rows in the five tables named ( table504s and table694s ... table 2644s) respectively.

I think if you look at what I'm trying to do there it should be self explanatory. I realize what is wrong, I just don't know how to tell the formula to only match the result if it falls in the totals row of one of the five tables that are stacked in the middle of my sheet.

I'm not sure I expressed myself eloquently enough to be understood but having pinpointed the problem cells I hope this is self explanatory.

I did this same report for March and it was fine, because I had all unique numbers, only now am I seeing the flaws in these newly created formulas. Forgive my formulas if they are not as neat and clean as should be, I am brand new and these are my very first few weeks creating this stuff and well, thanks for all your help so much!

**JBeaucaire** · 04-29-2013, 03:15 AM

IN AG7:

=INDEX(OFFSET($P$1,MATCH(MIN(Table504S[[#Totals],[Jobs Sold]],Table696S[[#Totals],[Jobs Sold]],Table1506S[[#Totals],[Jobs Sold]],Table2351S[[#Totals],[Jobs Sold]],Table2644S[[#Totals],[Jobs Sold]]),S:S,0)-1,,20), MATCH("TOTAL PROFITS", OFFSET($P$1,MATCH(MIN(Table504S[[#Totals],[Jobs Sold]],Table696S[[#Totals],[Jobs Sold]],Table1506S[[#Totals],[Jobs Sold]],Table2351S[[#Totals],[Jobs Sold]],Table2644S[[#Totals],[Jobs Sold]]),S:S,0)-1,,20), 0)+2)

The colored range sets a range of 20 cells starting from the row where the MIN value is found. Then it searches those 20 cells for the fixed value TOTAL PROFITS and displays the value in the cell 2 cells below that.