Forum Discussion

DBreezy92's avatar
DBreezy92
Copper Contributor
Apr 08, 2021

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?

 

 

 

 

 

 

excel
Formulas and Functions

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 08, 2021

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 08, 2021

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

     

     

Resources