• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match 3D: check it out! see HyperLink

Stephan

Member
Index Match 3D! check it out!

www.hkrebs63.karoo.net/files/IndexMatch3D.zip

If it's not obvious how I've achieved this, as after all you might think intially this isn't possible, a fair assumption, as an IndexMatch compares each row within source If > depending on cell value/combobox selection within "A" 1 tab, results in each row being a YES or NO with a max number of rows that meet the YES criteria, see formula explained below.

Anyway! Let me explain, and take away the guess work out of it!

see TABS: SRCH WRK or SRCH BUS
Combo Boxes V1 & W3 select Dependent Data from 12 Tabs:
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec:
Srch Wrk selects records/number: Jan-Dec > Columns DC & DB
Srch Bus selects records/number: Jan-Dec > Columns DG & DF

In other words in plain English, this is how it works!
1 ComboBox V1 is MTH criteria > NestedIf Formula within same Tab
1 ComboBox W3 is either STAFF/BUSINESS criteria > If within each Mth: Jan>Dec.
and NestedIf in Srch Wrk/Bus in Columns A-T selects correct mth data in pre-prepared Columns AJ+

My simple approach, Index Match Report, then just SUM total from Report, without SumIf or SumProduct! Far better performance & more efficient then Merging tabs of identical structure to report on contents.

It's all formula in just Index Match, If and CountIf, and it's very quick, even in large data sets, even with a years worth of information, over Multiple Tabs. simple eh!

www.hkrebs63.karoo.net/files/IndexMatch3D.zip

You may still wonder how this has been achieved, simply Nested If, when you see it, you'll realise it's so obvious, why didn't you think of it also. And when you've done your own edit, just copy your TAB, add further IF columns to your SOURCE data, then change your search tabs in JAN-DEC as described below:

But how to edit for your own purposes? say if you wanted another search page?
for example SRCH BUS tab just use: EDIT, REPLACE to change just 2 VALUES to your own TAB/CELL: in this instance to change all within this worksheet
EDIT, REPLACE, WORKSHEET:
from: $DG$1
to: your multi tabs column max ref, this tells Index Match the max number of records that meet criteria

and

EDIT, REPLACE, WORKSHEET:
from: $DF:$DF
to: your multi tabs column records YES or NO ref, this tells Index Match which rows that are revelant to criteria, ie this row is a YES.

what will this do? change the cells automatically instead individual row edits! all done in seconds instead of hours!

For example in SRCH BUS: Nested If from Columns AJ for all the 12 different MTHS:
For example JAN 1st Column
=IF(AND($AJ$1=0),"",IF(ROWS('SRCH BUS'!$W$3:$W3)>JAN!$DG$1,"",INDEX(JAN!A:A,MATCH(ROWS('SRCH BUS'!$W$3:$W3),JAN!$DF:$DF,0))))
 
Back
Top