PDA

View Full Version : Reverse INDEX and MATCH



Robert87
09-18-2017, 11:04 AM
Hey.

I want to extract data from a list to a schedule using a kind of reverse Index and match.

I donŽt really know how to explain this in English, so IŽll just post a file showing you what I need.

20388

YasserKhalil
09-18-2017, 11:34 AM
In cell H4 put this array formula

=IFERROR(INDEX(Table1[Name],MATCH(1,(Table1[Date]=$F4)*(Table1[Time]=H$3)*(Table1[Room]=$F$2),0)),"")

Press Ctrl + Shift + Enter

In H12

=IFERROR(INDEX(Table1[Name],MATCH(1,(Table1[Date]=$F12)*(Table1[Time]=H$11)*(Table1[Room]=$F$10),0)),"")

And drag to left and down
You can do the same for the third table

Bob Phillips
09-19-2017, 12:41 AM
Non-array form

=IFERROR(INDEX(Table1[Name],SUMPRODUCT(--(Table1[Date]=$F12),--(Table1[Time]=I$11),--(Table1[Room]=$F$10),ROW(Table1[Date]))-1),"")