# Index Match formula where the match searches multiple columns

Copper Contributor

# Index Match formula where the match searches multiple columns

Hi,

I am comparing two tabs to find matching items and then return information from certain columns, and I will typically use Index Match for this sort of exercise.

The issue I am dealing with on a specific workbook is that the lookup value in the match function is stored in one of several columns. I have made sure that all cells have no hidden spaces and are all formatted the exact same same. I am using a formula that looks something like;

=index('sheet1'!A:A,match(sheet2!A2,'sheet1'!B2:E:5000,0))

I will then hit CTRL + Shift + Enter but will receive an #N/A result.

Can anyone help with this or offer an alternative (better) solution?

2 Replies

# Re: Index Match formula where the match searches multiple columns

MATCH() works with 1D array only. If in the same row value appears only one time in any of columns, formula could be

=IFNA(INDEX('sheet1'!A2:A5000,
match(1,
INDEX(
(sheet2!A2='sheet1'!B2:B5000,0)+
(sheet2!A2='sheet1'!C2:C5000,0)+
(sheet2!A2='sheet1'!D2:D5000,0)+
(sheet2!A2='sheet1'!E2:E5000,0), 0
), 0
),"no such")

# Re: Index Match formula where the match searches multiple columns

MATCH does not work with a range of more than one row and column.

Try this (it is an ordinary formula):

=INDEX(Sheet1!A2:A5000,SUMPRODUCT((Sheet1!B2:E5000=A2)*(ROW(Sheet1!A2:A5000)-ROW(Sheet1!A1))))