index match double criteria not working, what is the problem?

0
votes

I am trying to use index match with multiple criteria to find a matching value.

its regarding stock data, the data on different companies is all vertically compiled, therefore I need to match on DATE and ID. I already converted all the dates into regular numbers.

When I use the normal index/match combo (with only DATE/ID) I get the first match, as it is supposed to do.

=INDEX('Stock Data'!$A$1:$F$63727;MATCH('Event Data'!I22;'Stock Data'!$B$1:$B$63727;0);6)

However, once i rewrite it to add the first criteria into:

=INDEX('Stock Data'!$A$1:$F$63727;MATCH(1;('Event Data'!I22='Stock Data'!$B$1:$B$63727);0);6)

I keep getting N/A, consequently the same happens when I try to add the second criteria

=INDEX('Stock Data'!$A$1:$F$63727;MATCH(1;('Event Data'!I22='Stock Data'!$B$1:$B$63727)*(J$5='Stock Data'!$D$1:$D$63727);0);6)

What is the problem here? I already checked for the correct formats of the cells which seems to be the same (both numbers)

events data Stock data: https://i.stack.imgur.com/8k4qL.png

1
excelexcel-formulamatching
show what the data looks like - e_conomics
Are you array entering {CTRL+SHIFT+ENTER} dual match condition formula? - shrivallabha.redij
I also tried to add the array, but im having the same problem, I also added two screenshots of the data. This is the document: dropbox.com/scl/fi/qza3vgkyewjj4mokp3mwg/… - Bram

1 Answers

0
votes

Please refer to your attached file

In J15, formula copied across right and all copied down :

=IFERROR(INDEX('Stock Data'!$A$1:$F$63727,MATCH(1,INDEX(($I15='Stock Data'!$B$1:$B$63727)*(J$5='Stock Data'!$D$1:$D$63727&""),0),0),6),"")