It's relatively straightforward I think given the aim is to avoid arrays.
You're criteria are set on 'Expected Results' such that:
The data that needs to be filtered is on sheet 'Data Set', the key data is listed as follows:
So in Column I on 'Data Set' (the first blank column) we add an incrementing INDEX...
So the value generated by the above will increment only when the conditions specified on 'Expected Results' (B3:B5) are met by the values for a given row in Col A & C on 'Data Set' sheet.
The end result of the above formula when applied across all rows on the 'Data Set' sheet is that you have an INDEX key... each step in value denotes a new record which should appear on your 'Expected Results' table.
I opt for an incrementing flag approach as opposed to a 0 for those records that don't match criteria such that you can avoid the need to conduct say an additional MAX formula over the preceding range as and when you stumble across a valid record... the fewer calcs the better
So given you know have an INDEX key on 'Data Set' you can now populate the 'Expected Results' table without need for array, the table is setup such that:
Given the above you can setup one formula and apply across the entire matrix using a combination of Absolute & Relative References, so the formula for the first cell in the results table:
So essentially where a record is required an INDEX / MATCH approach is used... where INDEX works along the lines of:
so for example:
In your case the Index range is variable for each column given in Column A you want to return a value from Column A on 'Data Set' sheet, in B you want to return Column B on 'Data Set' etc... so although the rows remain constant for each formula the column will vary as the formulae is copied across, hence:
You can see that $2 and $1000 ensure the rows are fixed but as the formula is copied to the right A will change to B and B to C etc...
To establish the ROW to return from the INDEX range we can (given our INDEX key method on 'Data Set') use the count of ROWS we've used thus far the 'Expected Results' Table thus far in a MATCH function against our Index key...
Will return 1... as the formula is copied down from A10 to A11 the return will change to 2 and say when we reach A20 the value will be 10 given A10:A20 equates to 10 rows.
So:
Will return the row position of "1" in our INDEX Key range on 'Data Set' sheet... this we use in the INDEX:
The column value for our INDEX is set to 1 given our INDEX range is only 1 column (A2:A1000) -- in this instance (1 column) the column value could be omitted altogether or set to 0, however, I always prefer to specify the value regardless.
The only other thing to add is that the 'Expected Results' table may be setup to return more rows than a given set of criteria will generate... so to avoid returning errors and to avoid conducting unnecessary calculations we hold in one cell the MAX Index key value as this tells us how many records we should be returning in the table:
Given you have the above value you can tweak the Table formulae such that they only fire if ROWS(A$10:A10) returns a value <= the MAX value... so if you have 5 records (as was the case in the sample file) then when you reach A16:
So:
The above says - if ROWS > MAX return a null value... only if a record will exist should you conduct the INDEX formula.
I hope the above helps some.
None of the formulae used here are particularly advanced so you will find plenty of examples of each on-line and XL Help is obviously a good resource. The way they are used in conjunction is perhaps a little more complex, as such it's always better to break down formulae into component parts and evaluate each component part separately.
Bookmarks