What is this Index-match doing?

[Richard U]

I've stripped out all the path information and identifiable information.

This is referencing an external workbook, but I have never seen an index match written like this before.


Could someone please explain what this is doing?

=INDEX('[ExternalFile.xlsx]Consolidation'!$F:$F,MATCH(1,(A2='[ExternalFile.xlsx]Consolidation'!$B:$B)*(B2=[ExternalFile.xlsx]Consolidation'!$D:$D),0))

 

[steve the fish]

Re: Help needed: What is this Index-match doing?

This part:

(A2='[ExternalFile.xlsx]Consolidation'!$B:$B)*(B2=[ExternalFile.xlsx]Consolidation'!$D:$D)

is multiplying two arrays of TRUE/FALSE results. To excel TRUE is 1 and FALSE is 0. So if you multiply TRUE x TRUE you get 1. Anything else produces 0. So if you use MATCH(1 you will get the position in the arrays where the two tests are TRUE. The INDEX then does its work.

 

[Toadstool]

Re: Help needed: What is this Index-match doing?

Hi Richard U,

It's missing the curly brackets as it would need to be entered with Ctrl-Shift-Enter to work.

The INDEX is clear enough as it's getting a cell from column F of the external file..

The MATCH(1, is checking for a 1 to be returned from the next part of the check

If A2 is found in the external file in column B and on the same row B2 equals D then multiplying the logical 1 by 1 gives 1, so it finds a MATCH with MATCH(1, so returning the row number for the INDEX.

It has to be found on the same row otherwise (A2='[ExternalFile.xlsx]Consolidation'!$B:$B)*(B2=[ExternalFile.xlsx]Consolidation'!$D:$D) would give a (0*1), a (1*0) or a (0*0) all of which result in zero so no MATCH to 1.

 

[Richard U]

Re: Help needed: What is this Index-match doing?

Thanks, the curly brackets didn't copy over, and I didn't notice.