Multiple Match and return header

  • Thread starter Thread starter edethington
  • Start date Start date
E

edethington

I am trying to find a way to Match on multiple values and return the
header a row based on that. I have tried looking at combinations of
vlookup, match, and index and so far haven't been able to figure out
anything that can do what I need. I have a list of data with a
person's name in the first column, and then headers for an action they
took. Then data in each cell is then the date range they took the
action. here is an example:


Action 1 Action 2 Action 3 Action 4
John 2/3/2005 2/5/2006 5/7/2006 9/25/2006
Bob 1/3/2006 5/3/2006 8/10/2006 1/5/2007
Sue 3/3/2006 9/5/2006 9/7/2006 9/25/2006
Jim 1/3/2007 5/3/2007 8/10/2007 9/5/2007

So on another worksheet, I want to reference this data and have a row
that has:

Bob 1/3/2006

In this case I would want it to return to me "Action 1" to be
displayed.

In addition to just being able to match, I actually also need to to do
a closest match on the date so if I have:

Sue 5/5/2006

I could match on it is it would still return the value of "Action 1"
being the previous value for this person, even though it isn't an
exact match. Can someone let me know if this is possible? Thanks for
your help!
 
So, if there isn't an exact date match then match the closest date that is
*less* than the lookup date? I notice that your dates are in ascending
order...

With this table in the range A1:E5 -
Action 1 Action 2 Action 3 Action 4
John 2/3/2005 2/5/2006 5/7/2006 9/25/2006
Bob 1/3/2006 5/3/2006 8/10/2006 1/5/2007
Sue 3/3/2006 9/5/2006 9/7/2006 9/25/2006
Jim 1/3/2007 5/3/2007 8/10/2007 9/5/2007
Click to expand...

A8 = Sue
B8 = lookup date = 5/5/2006

=INDEX(B1:E1,MATCH(B8,INDEX(B2:E5,MATCH(A8,A2:A5,0),0)))
 
Back
Top