Reputation: 109
I have two table that are vertical to one another. I make the following 3rd table from the formula below (Also see picture):
=INDEX($C$3:$C$30,MATCH(1,($I3=$A$3:$A$30)*($K3=$E$3:$E$30)*(L$2=$D$3:$D$30),0))
What I need help with is how to make this formula get data from a horizontal set of tables or tables from different worksheets:
Can I chain together ranges like so?
=INDEX($D$3:$D$14:$M$17:$M$28,MATCH(1,($J3=$A$3:$A$14:$J$17:$J$28)*($L3=$F$3:$F$14:$O$17:$O$28)*(M$2=$E$3:$E$14:$N$17:$N$28),0))
It's not working and I know there MUST be a way to do it.
Upvotes: 0
Views: 2000
Reputation: 109
The information I provided was incorrect for the second table in the Day column this might have been the reason it wasn't initially working. I have fixed it and used the suggestion given by Scott Craner!
The function that works is:
=IFERROR(INDEX($D$3:$D$14,MATCH(1,($J3=$A$3:$A$14)*($L3=$F$3:$F$14)*(M$2=$E$3:$E$14),0)), INDEX($M$17:$M$28,MATCH(1,($J3=$J$17:$J$28)*($L3=$O$17:$O$28)*(M$2=$N$17:$N$28),0)))
Upvotes: 1