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 their own "customer item code" (there may be multiple customers using the same item code) and a unique "customer ID number."

 

In another sheet I have a table with a separate row for each customer along with several items of interest. I am hoping to find a way to ID a specific row in the Index sheet by searching the whole Index range for a row that contains both the "customer item code" AND the "customer ID number". The issue is that because we have many columns of data that could contain a "customer item code" I cannot just Match to a specific column - I need to have the formula search the whole range for a row that happens to have both those numbers in it. In the end, I would like the formula to return the "primary item code" that coordinates with that specific customer's item code.

 

Is this even possible??

 

Thanks for any help you can offer!

 

 

excel

1 Reply

  • 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