Multiple Criteria Lookup (Using Table Names and Column names (not $A1:C23) to return Array of values

[Jcbperault]

I’ve been really struggling with a particular search using multiple criteria on an actual TABLE using Table Names and Column Names. This appears to work differently than when using the A1:B200 notation for some reason.

I need to do it like this in order to accommodate adding of rows and columns without needing to adjust the formulas.

And... to make it even more difficult I need one of the criteria to be within a numeric range.

GOAL - Find all the items that match the following criteria
Type = ON
Material = Aluminum
OD +/- ODTolerance = 23 +/- 0.02
There are 45 of these in the list. I need the result to be a list of Devices that match all this criteria.

I've tried a large number of Index/Match/Countif/Countifs/etc... But cannot get it to work.

Here is the [Ctrl][Shift][Enter] example of one cell

=IFERROR(INDEX(_DLookup[Hex], SMALL(IF(COUNTIF(_DLookup[Hex Type],_HT)*COUNTIF(_DLookup[Material],_HM)*COUNTIFS(_DLookup[OD (mm)],">="&_O-_ODt,_DLookup[OD (mm)],"<="&_O+_ODt), ROW(_DLookup[Device])-MIN(ROW(_DLookup[Device]))+1), ROW(A1)), COLUMN(A1)),"-")

I'm pretty sure that the countifs part in here is messing up the whole thing, but I'm not sure what to do about it.

Any help would be GREAT.
Thanks

 

[bosco_yip]

Try,

In G9, enter non-array formula and copied down :

=IFERROR(INDEX(_DLookup[Hex],AGGREGATE(15,6,ROW(_DLookup[Device])-MIN(ROW(_DLookup[Device]))+1/(_DLookup[Hex Type]=_HT)/(_DLookup[Material]=_HM)/((_O+_ODt)>=_DLookup[OD (mm)])/((_O-_ODt)<=_DLookup[OD (mm)]),ROWS($1:1))),"-")

or,

Array formula (SHIFT+CTRL+ENTER)

=IFERROR(INDEX(_DLookup[Hex],SMALL(IF((_DLookup[Hex Type]=_HT)*(_DLookup[Material]=_HM)*((_O+_ODt)>=_DLookup[OD (mm)])*((_O-_ODt)<=_DLookup[OD (mm)]),ROW(_DLookup[Device])-MIN(ROW(_DLookup[Device]))+1),ROWS($1:1))),"-")

Regards
Bosco

 

[Jcbperault]

Bosco, that is perfect! Thanks. I hope this helps others. There are MANY, MANY Excel tips out there on how to use arrays and how to search with multiple criteria, but I have never seen one that uses table names and column names to do it.

Thanks again.
-Joe

 

[Peter Bartholomew]

Joe

Using structured references is not so different from using named ranges. In fact if the structured references are so long that they obscure your formula you can apply names to them such as




These resize with the table. I develop array formulas on the worksheet such as
= IF( (§HexType=_HT) * (§Material=_HM) * (§OD>=_O-_ODt) * (§OD<=_O+_ODt), k )
but, once it is working, transfer it to a named formula (here called 'matches').
The record index array 'k' is given by


Having bitten the bullet and accepted setup costs, the final worksheet formula is a reasonably readable
= INDEX( §Hex, SMALL( matches, k ) )

If you do not like ending with NUM! errors it is always possible to count the number of matches and modify the formula to read
=IF( k<COUNT(matches), INDEX( §Hex, SMALL( matches, k ) ), "" )

In essence, there is no difference between this solution and that proposed by Bosco but I like to use named formulas (they are always evaluated as arrays) to simplify the final formula by breaking it into a sequence of more readable steps.

Peter

 

[Jcbperault]

Thanks - that definitely makes the formula a bit easier to read.