Index Match formula where the match searches multiple columns

Copper Contributor

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

@DBreezy92 

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")

 

 

@DBreezy92 

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))))