Forum Discussion
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...
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.
Thanks lot SergeiBaklan
- I can do it, thanks for the help mtarler
- 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 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.
- =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. NPfor , you are welcome
