This one has been boggling my brain for a while now. I'm trying to place a value into cell D1 based on the value found in A1.
I have three columns:
A
Red
Orange
Blue
Green
Red
Orange
B
4/12
4/12
4/12
4/12
4/13
4/13
C
Robert
Michael
John
Sara
Tom
Jim
I am trying to create a formula in D1 that searches for the value in A1 with the MAX Date in B returning the value in C from the Max Date row. So in this case D1 would return"Tom".
Is this possible using vLookup?
Last edited by tbarnes22; 04-24-2013 at 08:41 PM.
Try this array* formula in D1:
=INDEX(C1:C6,MATCH(A1&MAX(IF((A1:A6=A1),B1:B6,0)),A1:A6&B1:B6,0))
*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>.
The attached file demonstrates.
Hope this helps.
Pete
You are a genius! Thank you so much, this worked perfectly.
To add on to this, is there a way to only return the value in the D column if the row contains the max date? So in this case D1 would return nothing, and D5 would return "Tom".Originally Posted by Pete_UK
Put this array* formula in D1:
Formula:
Please Login or Register to view this content.
then copy down to D6.
*Use CSE to commit, as previously advised.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks