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
kalyansp's avatar
kalyansp
Copper Contributor
Jun 08, 2023

SergeiBaklan 

 

Hi Sergei, What does the INDEX function inside the MATCH function signify and How can we use INDEX inside MATCH or INDEX function?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 08, 2023

kalyansp 

That's the trick for the Excel which doesn't support dynamic arrays, e.g. Excel 2016. If in such Excel we use formula like

=INDEX(A:A, MATCH( 1, (B:B=2)*(C:C=3), 0 ) )

here (B:B=2)*(C:C=3) is an array and we shall use above formula as an array one, i.e. to enter with Ctrl+Shift+Enter.

However, with INDEX( (B:B=2)*(C:C=3), 0 ) which returns exactly the same array we may use formula

=INDEX(A:A, MATCH( 1, INDEX( (B:B=2)*(C:C=3), 0 ), 0 ) )

as regular one, i.e. enter it simply with Enter.

Above is not required for the Excel which supports dynamic arrays (Excel 365).

And on practice please don't use entire columns as in above sample, that significantly affects the performance. Structured tables, dynamic ranges or fixed ranges instead.

Resources