Forum Discussion

mangofruit's avatar
mangofruit
Copper Contributor
Jun 07, 2021

Can you return a value based on a multiple column match??

I have an Index sheet with a row for each product by "primary item code" and columns for information about each item. Extending to the right, we have columns of customer-specific data, including thei...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 07, 2021

mangofruit 

There are probably two (or more) distinct approaches.  One is to unpivot the table to have one record per product/customer combination and the other is to pick out customer and code arrays from the index.

= LET(
  k, SEQUENCE(ROWS(IndexTable)),
  indexCustomer, INDEX(IndexTable, k,{2,4}),
  indexCode, INDEX(IndexTable, k,{3,5}),
  selected, MMULT((indexCustomer=customer)*(indexCode=Code),{1;1}),
  FILTER(productCode,selected))

MMULT is used to collect the data together from multiple columns and FILTER looks up the non-zero entry to return the product code.

Note: The formula is written with 365 in mind because that is all I use.

Resources