Can I do INDEX MATCH with 3 MATCHES?

[mteden]

I am trying to use INDEX MATCH with three MATCHES and it does not seem to be working. It returns the #REF error.

I have a table of data with titled rows and columns with 2 titles. The first match I do is for the row, then I do 2 separate ones for the columns. Where the rows and columns match it returns a 0 entry, because there is no entry in that field. For all others it returns #REF . However, one common thing is that all the other fields have formulas in them. Is INDEX MATCH not able to return data from a formula field? Will I have to past the table as data first for this to work? Otherwise I have checked many times and all INDEX and MATCH references seem to be accurate. Very confused, if anyone can help I'd be grateful.

Thanks
JC

 

[DanteAmor]

You can give an example of how you have the data, what you want to look for and the expected result.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[cyrilbrd]

Given a dataset in a table with 3 conditions.
column 1 is range cdt1
column 2 is range cdt2
header is range condition 3

=SUMPRODUCT((RNGcdt1=cdt_1)*(RNGcdt2=cdt_2)*(RNGcdt3=cdt_3),Range)

 

[Robby19]

Would be beneficial to know what/where you want to index and match, but try this;

=INDEX(A:A,MATCH(1,(B:B=J1)*(C:C=J2)*(D:D=J3),0))

Enter with Ctrl + Shift + Enter for an array formula.

The J references should be the 3 cells that you want to match, compared to the columns you are looking at. If that makes sense.

Edit 1: To answer your question, Index Match can be used with the results of cells with formulas.