Return a Value from Column B if the search Value is found in any Column through Z-AG

Question

Here is the scenario that I have been trying to puzzle out.

In Sheet1 Column B, I have a list of SKUs, in Sheet2 Columns Y-AG I have multiple lists of SKUs that could match what is in Sheet1 Column B.

I'm trying to return the value of Sheet2 Column B if any of the values in Sheet2 Columns Y-AG match Sheet 1 Column B.

I've tried the following formula, but alas it returns only errors.

=INDEX('Sheet2'!$B:$B,MATCH($C2,'Sheet2'!Y:AG,0),1)       

Thank you for your help kind people!

Answer 1

Combination of MMULT (Matrix Multiplication) & INDEX, MATCH finds value from multiple columns.

---

N.B.

The used formula finds Name, Sanjay in cell A67, in Sheet 1 A65:A72, matched with Name, Sanjay in B39, in Sheet 2 B37:D44, and returns value Kim from Sheet 2 Column A (A37:A44), in Sheet 1 Column B in Cell B67.

• An array (CSE) formula, in Sheet 1 cell B65:

  {=IFERROR(INDEX(Sheet2!$A$37:$A$44,MATCH(1,MMULT(--(Sheet2!$B$37:$D$44=Sheet1!A65),TRANSPOSE(COLUMN(Sheet2!$B$37:$D$44)^0)),0)),"")}
  

• Finish formula with Ctrl+Shift+Enter & fill it down.

Adjust cell Sheet name & cell references in the formula as needed.

---

How the formula works:

• (Sheet2!$B$37:$D$44=Sheet1!A65), works inside out, where Sheet1!A65 works as criteria, finds name in Sheet 2 B37:D44, generates TRUE/FALSE for every value in B37:D44.

• The COLUMN function creates 2nd numeric array, has dimension of 3 col X 1 row .

• TRANSPOSE converts this array to 1 col X 3 rows.

• The power of zero simply converts all numbers in the array to 1.

• The MMULT function is then used to perform matrix multiplication.

• IFERROR, as usual handle "N/A" error.