Formula to pull last date (of many) from data sheet into main workbook

[gigi79]

Hi all,

I have a question,

I have a tracking sheet that tracks decision and dates, this sheet feeds into a mater workbook that has s decision and decision date columns.

The problem/question is, that there can be reschedules, appeals, etc so there could be more than one decision date (in the tracking sheet). In some situations there may just be ONE decision, in others there can be two or three.

I need the master tracker to only pull the final decision and date (so I would need it to look in all columns and only pull the latest entries)

The tracking sheet looks like this

ID	Decision	Decision Date	Decision 2	Decision Date 2	Decision 3	Decision Date 3
123456	Withdrawn	4/20/2015	dismissed	04/25/2015	Reversed	4/30/2015
234578	Upheld	5/1/2015
789457	Upheld	4/28/2015	Reversed	5/3/2015

<tbody>
</tbody>

The master sheet appears as below

ID	Decision	Decision Date
123456
234578

<tbody>
</tbody>

The current formula (below) is a Vlookup that looks the persons ID and then the decision and date - This is the formula I inherited along with the workbook

=IF($R3150="","",IF(VLOOKUP($R3150,'O:\Private\Document location\[Decisions.xlsx]Hearing'!$F$6:$K$4999,3,FALSE)="","",IFERROR(VLOOKUP($R3150,'O:\Private\document location\[Decisions.xlsx]Hearing'!$F$6:$K$4999,3,FALSE),"")))


Any help is much appreciated.

 

[gigi79]

None of the suggestions work. Im not sure if I was clear that the data sheet containing the decisions and dates are in a different workbook.

This is how I adjusted the formulas...still getting NA.

=VLOOKUP($R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$M$4999,COUNTA(INDEX('O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$G$6:$M$4999,MATCH($R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$M$4999,0),)))



The actual data sheet has un-needed data in columns A-E. The data needed to be pulled is in columns G-M

Here are better ideas of the workbooks

Workbook 1 (data)

last name

first name

memberid

phone

random number

lookup number

decision date

decision

decision date 2

decision 2

decision date 3

decision 3

random

random3

random4

random 5

random 6

girl

johnson

xxxxxx

xxxxxxx

xxxxxxx

784552

3/18/2015

upheld

xxxx

cxxxx

xxxx

yyyh

boy

james

xxxxx

xxxxxx

xxxxx

123456

3/15/2015

withdrawn

4/1/2015

denied

person

three

xxxxxx

xxxxx

xxxxxx

891011

5/1/2015

dismissed

5/3/2015

reversed

5/15/2015

upheld

<tbody>
</tbody>

Workbook 2:
The sheet that needs to be populated with the dates and decisions (ranging from columns G-m is below) This is a different workbook

last name	first name	memid	phone	random number	lookup number	decision date	decision	random	random
girl j	johnson	xxxxx	xxxxx	xxxxx	784552			xxxx	xxxx
boy	james	xxxx	xxxx	xxxxx	123456			xxx	xxxx

<tbody>
</tbody>

 

[gigi79]

I think the confusion is that you all thought they were on the same sheet?

 

[Trouttrap2]

I think you were clear about it being in two different workbooks. What is unclear is the range areas. What column is the lookup number in work book 1.

 

[gigi79]

I think you were clear about it being in two different workbooks. What is unclear is the range areas. What column is the lookup number in work book 1.

It is in column F on that workbook

 

[Trouttrap2]

try this, using a modified Aladain's solution and using your ranges. I think those are your ranges. You will have to determine that..
Decision =LOOKUP("Ω",INDEX('O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$G$6:$M$4999,MATCH(R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$F$4999,0),))
Decision Date =LOOKUP(9.99999999999999E+307,INDEX('O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$G$6:$M$4999,MATCH(R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$F$4999,0),))

 

[Aladin Akyurek]

I think the confusion is that you all thought they were on the same sheet?

Try to implement yourself the suggestion in different books instead of one. That's really a small step to take...

 

[gigi79]

This worked.

Thank you!!!!

 

[gigi79]

try this, using a modified Aladain's solution and using your ranges. I think those are your ranges. You will have to determine that..
Decision =LOOKUP("Ω",INDEX('O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$G$6:$M$4999,MATCH(R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$F$4999,0),))
Decision Date =LOOKUP(9.99999999999999E+307,INDEX('O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$G$6:$M$4999,MATCH(R266,'O:\Private\Hearings\Tracker\[decisionsTESTING.xlsx]FairHearing'!$F$6:$F$4999,0),))

I meant to quote you.

Thanks again

 

[Aladin Akyurek]

I meant to quote you.

Thanks again

That's great, but you are quoting wrong. By the way, it is not Ω, it's REPT("z",255)...