Forum Discussion

David's avatar
David
Copper Contributor
Oct 27, 2017

Can you use AND / OR in an INDEX MATCH

Hi I have am array formula that looks like this:   =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works.   I want to add...
Excel Desktop
Formulas & Functions
NPfor's avatar
NPfor
Copper Contributor
Aug 24, 2023

hello SergeiBaklan 

I need help to add one more condition in Index and Match function.

B2 cell formula is working well

sheet1: b3=INDEX(Sheet2!$O$2:$AJ$2488,MATCH(Sheet1!D3,Sheet2!$J$2:$J$2488,0),MATCH(Sheet1!A3,Sheet2!$O$1:$AJ$1,0))

But I need to add one more condition-date range-Workdate need to within (sheet2) Start_date and end_date

I did try to put formula as array B2==INDEX(Sheet2!$O$2:$AJ$8,MATCH(1,INDEX((Sheet1!D2=Sheet2!$J$2:$J$8)*((C2>=Sheet2!$K$2:$K$6)*(Sheet1!C2<=Sheet2!$L$2:$L$6))*(Sheet1!A2=Sheet2!$O$1:$AJ$1),0),))

But didn't work. 

Below is worksheet link

 

https://docs.google.com/spreadsheets/d/1KLxXNEjwuLOMMJqwAhDtt_M2f9YjlV6N/edit?usp=sharing&ouid=111838350069666419683&rtpof=true&sd=true 

Thanks Lot

  • ricardo2260's avatar
    ricardo2260
    Copper Contributor
    Jan 17, 2024
    I can do it, thanks for the help mtarler
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 17, 2024
    so I see you are actually using google sheets and not excel so i don't even have to ask what version, which is good because you should use some newer functions. if I'm reading your formula right try:
    =LET(first, XLOOKUP(B20, 'NM & CRM'!$D$4:$D, 'NM & CRM'!$C$4:$C,""), IF(first<>"",first, XLOOKUP(B20, 'NM & CRM'!$I$4:$I, 'NM & CRM'!$H$4:$H,""))
    basically do the first lookup and assign it to 'first' then check if a value was found and if not do the second lookup.
  • ricardo2260's avatar
    ricardo2260
    Copper Contributor
    Jan 16, 2024

    ricardo2260 I want to retrieve data in these 2 tables with options, search in table A, if there is, retrieve column data, if there is none, search in table B, if found in column B, retrieve the column.

  • ricardo2260's avatar
    ricardo2260
    Copper Contributor
    Jan 15, 2024
    =INDEX('NM & CRM'!$B$4:$D;MATCH(B20;'NM & CRM'!$D$4:$D;0);2)&INDEX('NM & CRM'!$G$4:$I;MATCH(B20;'NM & CRM'!$I$4:$I;0);2)

    Sir, can you help me improve this formula, because when I run the formula, 2 names appear. I only want 1 name.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 25, 2023

    NPfor , you are welcome

  • NPfor's avatar
    NPfor
    Copper Contributor
    Aug 24, 2023

    Thanks lot SergeiBaklan 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 24, 2023

    NPfor 

    That could be

    =INDEX(
    'Maximo Rate table'!$O$2:$AJ$2488,
    MATCH( 1,
        INDEX(
            (D2 = 'Maximo Rate table'!$J$2:$J$2488) *
            (C2 >= 'Maximo Rate table'!$K$2:$K$2488) *
            (C2 <= 'Maximo Rate table'!$L$2:$L$2488),
            0 ),
    0 ),
    MATCH(A2, 'Maximo Rate table'!$O$1:$AJ$1, 0)
)

    First MATCH is for rows with all conditions, second MATCH is for columns.

    Formula is in column I (I2, etc) in attached.

Resources