0

I have a function that will take a value from Column B in Sheet1 and search for a match in Column D of Sheet2 and return that value if found.

=INDEX(Sheet2!$D:$D,MATCH(B2,Sheet2!$D:$D,0))

However, I actually the function to search for a match in several columns of Sheet2 (D-Z), not just Column D.

Thanks in advance for your assistance!

2

2 Answers 2

1

This is a bit overkill. Concatenating the index match should work.

=IFERROR(INDEX(Sheet2!$D:$D,MATCH($B2,Sheet2!$D:$D,0)),IFERROR(INDEX(Sheet2!$E:$E,MATCH($B2,Sheet2!$E:$E,0)),IFERROR(INDEX(Sheet2!$F:$F,MATCH($B2,Sheet2!$F:$F,0)),IFERROR(INDEX(Sheet2!$G:$G,MATCH($B2,Sheet2!$G:$G,0)),IFERROR(INDEX(Sheet2!$H:$H,MATCH($B2,Sheet2!$H:$H,0)),IFERROR(INDEX(Sheet2!$I:$I,MATCH($B2,Sheet2!$I:$I,0)),IFERROR(INDEX(Sheet2!$J:$J,MATCH($B2,Sheet2!$J:$J,0)),IFERROR(INDEX(Sheet2!$K:$K,MATCH($B2,Sheet2!$K:$K,0)),IFERROR(INDEX(Sheet2!$L:$L,MATCH($B2,Sheet2!$L:$L,0)),IFERROR(INDEX(Sheet2!$M:$M,MATCH($B2,Sheet2!$M:$M,0)),IFERROR(INDEX(Sheet2!$N:$N,MATCH($B2,Sheet2!$N:$N,0)),IFERROR(INDEX(Sheet2!$O:$O,MATCH($B2,Sheet2!$O:$O,0)),IFERROR(INDEX(Sheet2!$P:$P,MATCH($B2,Sheet2!$P:$P,0)),IFERROR(INDEX(Sheet2!$Q:$Q,MATCH($B2,Sheet2!$Q:$Q,0)),IFERROR(INDEX(Sheet2!$R:$R,MATCH($B2,Sheet2!$R:$R,0)),IFERROR(INDEX(Sheet2!$S:$S,MATCH($B2,Sheet2!$S:$S,0)),IFERROR(INDEX(Sheet2!$T:$T,MATCH($B2,Sheet2!$T:$T,0)),IFERROR(INDEX(Sheet2!$U:$U,MATCH($B2,Sheet2!$U:$U,0)),IFERROR(INDEX(Sheet2!$V:$V,MATCH($B2,Sheet2!$V:$V,0)),IFERROR(INDEX(Sheet2!$W:$W,MATCH($B2,Sheet2!$W:$W,0)),IFERROR(INDEX(Sheet2!$X:$X,MATCH($B2,Sheet2!$X:$X,0)),IFERROR(INDEX(Sheet2!$Y:$Y,MATCH($B2,Sheet2!$Y:$Y,0)),IFERROR(INDEX(Sheet2!$Z:$Z,MATCH($B2,Sheet2!$Z:$Z,0)))))))))))))))))))))))))

idea : if column D search fails (produce error), then look in E:E. If column E match fails look in column F ... until Z.

Hope it helps. ( :

0

You can use an Array (CSE) formula, combines INDEX, MATCH, MMULT & TRANSPOSE, to find Lookup value by matching across multiple Columns.

enter image description here

Formula in E27 in Sheet 1 :

{=INDEX(Sheet2!$D$21:$D$24,MATCH(1,MMULT(--(Sheet2!$E$21:$G$24=D27),TRANSPOSE(COLUMN(Sheet2!$E$21:$G$24)^0)),0))}

N.B. Finish the Formula with Ctrl+Shift+Enter and fill it down.

How it works:

--(Sheet2!$E$21:$G$24=D27)

  • Generates TRUE/FALSE and -- sign converts them into 1 & 0.
  • MMULT used to perform Matrix Multiplication, and the result goes into the MATCH function as an array, with 1 as the Lookup value.

TRANSPOSE(COLUMN(Sheet2!$E$21:$G$24)^0)

  • Where COLUMN is used to create a numeric array with 3 Col by 1 Row, and TRANSPOSE converts this array to 1 Col & 3 Rows.
  • Power of Zero converts all numbers in the array to 1.
  • MATCH returns the position of the 1st match, which corresponds to the row of the first match Row meets the Criteria.
  • Index returns the Area, Alex belong to and so further.

Adjust Cell references in the Formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .