Excel Formula to Pull Cell Data Based on Column Header (Date) and [Similar]Row Header (Name)

Occasional Visitor

Hello all,

This is the first time I've posted and, please forgive me if this is a problem that someone else has already had solved...  I have been looking for an answer for 2 weeks and I just don't think that I know the right question to ask to get to the end result.


I have a table that I have built that is an amalgamation of the data that I then split into two different tables.  I have column headers, that equal the date of the days of the week, that match the column headers on the subsequent two tables, but then I have row headers that are the full name of the employee on the big table and only the first name of the employee on the corresponding tables.


I have tried to wrap my mind around using the VLOOPUP, HLOOKUP, LOOKUP, and INDEX and MATCH combinations to figure out how to auto fill the data on the other 2 tables based on the single input into the main worksheet and it's just not getting my anywhere.  


Any help that anyone can offer would be thoroughly appreciated.  I've attached screenshots of the tables so that you can see what I'm working with.

1 Reply


Formula in column B: 



You can then use the value that formula returns as a lookup value using index match, use this formula for Secondary Table: INDEX($A$2:$H$18,MATCH(FILTER($I$2:$I$15,ISNUMBER(FIND(A3,$I$2:$I$15)),"NF"),$A$2:$A$18,0),MATCH('ORL Sales Board'!$B$2:$F$2,0))