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

G

gigi79

New Member
Joined
Sep 18, 2012
Messages
34
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

IDDecisionDecision DateDecision 2Decision Date 2Decision 3Decision Date 3
123456Withdrawn4/20/2015dismissed04/25/2015Reversed4/30/2015
234578Upheld5/1/2015
789457Upheld4/28/2015Reversed5/3/2015

<tbody>
</tbody>



The master sheet appears as below

IDDecisionDecision 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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Assuming your data lives in A1:G4, in your example. And what your are looking up lives in A9, A10, and on down.

The Decision field formula: = VLOOKUP(A9,$A$2:$G$4,COUNTA(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),)))
The Decision Date field formula:= VLOOKUP(A9,$A$2:$G$4,COUNTA(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),))+1)
 
Upvote 0
Trouttrap2 said:
Assuming your data lives in A1:G4, in your example. And what your are looking up lives in A9, A10, and on down.

The Decision field formula: = VLOOKUP(A9,$A$2:$G$4,COUNTA(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),)))
The Decision Date field formula:= VLOOKUP(A9,$A$2:$G$4,COUNTA(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),))+1)
Click to expand...


Not exactly, what I'm looking up is in a different workbook (decisions/tracking)alltogether that needs the dates filled.

I tried the formulas as they and tried to make it go to the other workbook, but I'm doing a whole lot wrong.
 
Upvote 0
You need to change the reference ranges. I tried, but have no way of testing.
Decison field: = VLOOKUP(A9,[Decisions.xlsx]Hearing'!$F$6:$K$4999,COUNTA(INDEX([Decisions.xlsx]Hearing'!$G$6:$K$4999,MATCH(A9,[Decisions.xlsx]Hearing'!$F$6:$F$4999,0),)))

Decision Date: = VLOOKUP(A9,[Decisions.xlsx]Hearing'!$F$6:$K$4999,COUNTA(INDEX([Decisions.xlsx]Hearing'!$G$6:$K$4999,MATCH(A9,[Decisions.xlsx]Hearing'!$F$6:$F$4999,0),))+1)
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
IDDecisionDecision DateDecision 2Decision Date 2Decision 3Decision Date 3
2​
123456​
Withdrawn
4/20/2015​
dismissed
4/25/2015​
Reversed
4/30/2015​
3​
234578​
Upheld
5/1/2015​
4​
789457​
Upheld
4/28/2015​
Reversed
5/3/2015​
5​
6​
7​
8​
IDDecisionDecision Date
9​
123456​
Reversed
4/30/2015​
10​
234578​
Upheld
5/1/2015​
11​

B9, copied down:

=LOOKUP(REPT("z",255),INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))

C9, copied down:

=LOOKUP(9.99999999999999E+307,INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))
 
Upvote 0
Aladin Akyurek said:
Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
IDDecisionDecision DateDecision 2Decision Date 2Decision 3Decision Date 3
2​
123456​
Withdrawn
4/20/2015​
dismissed
4/25/2015​
Reversed
4/30/2015​
3​
234578​
Upheld
5/1/2015​
4​
789457​
Upheld
4/28/2015​
Reversed
5/3/2015​
5​
6​
7​
8​
IDDecisionDecision Date
9​
123456​
Reversed
4/30/2015​
10​
234578​
Upheld
5/1/2015​
11​

<tbody>
</tbody>


B9, copied down:

=LOOKUP(REPT("z",255),INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))

C9, copied down:

=LOOKUP(9.99999999999999E+307,INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))
Click to expand...


Is it possible for you to explain this to me?

(sorry, I'm really not understanding).
 
Upvote 0
Trouttrap2 said:
You need to change the reference ranges. I tried, but have no way of testing.
Decison field: = VLOOKUP(A9,[Decisions.xlsx]Hearing'!$F$6:$K$4999,COUNTA(INDEX([Decisions.xlsx]Hearing'!$G$6:$K$4999,MATCH(A9,[Decisions.xlsx]Hearing'!$F$6:$F$4999,0),)))

Decision Date: = VLOOKUP(A9,[Decisions.xlsx]Hearing'!$F$6:$K$4999,COUNTA(INDEX([Decisions.xlsx]Hearing'!$G$6:$K$4999,MATCH(A9,[Decisions.xlsx]Hearing'!$F$6:$F$4999,0),))+1)
Click to expand...


I'm trying this now...
 
Upvote 0
Aladin Akyurek said:
Row\Col

A​

B​

C​

D​

E​

F​

G​

1​
ID
Decision
Decision Date
Decision 2
Decision Date 2
Decision 3
Decision Date 3

2​

123456​
Withdrawn

4/20/2015​
dismissed

4/25/2015​
Reversed

4/30/2015​

3​

234578​
Upheld

5/1/2015​

4​

789457​
Upheld

4/28/2015​
Reversed

5/3/2015​

5​

6​

7​

8​
ID
Decision
Decision Date

9​

123456​
Reversed

4/30/2015​

10​

234578​
Upheld

5/1/2015​

11​

<tbody>
</tbody>


B9, copied down:

=LOOKUP(REPT("z",255),INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))

C9, copied down:

=LOOKUP(9.99999999999999E+307,INDEX($B$2:$G$4,MATCH(A2,$A$2:$A$4,0),0))
Click to expand...
Should the A2 in the Match be replaced with A9 as the match reference?
 
Upvote 0
You must log in or register to reply here.

Similar threads

MKE_APA
  • Solved
Pull Data from another sheet if cell is blank
2
Replies
10
Views
860
MKE_APA
MKE_APA
M
  • Question
Color coding data pulled from multiple sheets
Replies
0
Views
218
MrsDSays
M
M
  • Question
Data info pulled from 2 sheets
Replies
3
Views
144
rlv01
R
T
  • Solved
Pulling Data from range given Date Criteria in Rows
Replies
6
Views
480
Kerryx
K
P
  • Question
Index-Match or Vlookup pulling from multiple sheets
Replies
2
Views
1K
aghaffar82
A

Forum statistics

Threads
1,214,661
Messages
6,120,790
Members
448,994
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top