arraysexcelmultiple-matches

How to return the column heading for multiple matches, from multiple criteria, in Excel?

I'm working with 40+ price lists for different groups of customers. By entering the item number and price, I'd like to show the names of all the price lists that match that item/price. I've set up the items and pricing lists like the table below. Some items have the same prices in multiple price lists, and some price lists do not list all items.

Item     ListPrice  Red    Blue   Green    Grey
Hosaka2  $200       $180   $188            $171
TrodesH  $460       $410   $380   $400     $380
TrodesL  $810       $680   $680   $720
Shuri    $80        $72           $72      $70

I'd like to enter TrodesH and $380 into cells and have the formula return Blue & Grey. Or enter Hosaka2 and $200 and have the formula return ListPrice.

I've used this Index array formula in the past to return multiple matches to a single entry. To, for example, list all the account numbers whose address matches the zip code I enter. It's very handy. But here I'm looking for the combination of item and price. Any ideas how I might move forward?

Edit: For better legibility in the Excel file, I've placed the above data in a worksheet named "Matrix" and the lookup formulas in a worksheet named "Check."

Solution

  • Let's assume that A1:F5 contains the data, and H2 contains the item of interest, such as TrodesH, and I2 contains the price of interest, such as $380, try...

    J2:

=COUNTIF(INDEX($B$2:$F$5,MATCH($H2,$A$2:$A$5,0),0),$I2)

K2, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=IF(COLUMNS($K2:K2)<=$J2,INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$5,MATCH($H2,$A$2:$A$5,0),0)=$I2,COLUMN($B$1:$F$1)-COLUMN($B$1)+1),COLUMNS($K2:K2))),"")

    Hope this helps!