Taren Shaw
Taren Shaw

Reputation: 109

How to INDEX(MATCH from two tables

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))

enter image description here

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))

enter image description here

It's not working and I know there MUST be a way to do it.

Upvotes: 0

Views: 2000

Answers (1)

Taren Shaw
Taren Shaw

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)))

enter image description here

Upvotes: 1

Related Questions