Look Up Help Required with newest date

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
414
Office Version
  1. 365
Platform
  1. Windows
Hi All

I would like to have a formula that looks up the horse Priory in workbook 1 and finds its speed value in workbook 2 (col H)
from the most recent date and place it in workbook 1 col H2. Unfortunately I am unable to sort workbook two but can sort workbook one in ascending order by horse name, also the dates in workbook two are in ascending order. I have attached an example as below and hope that helps with my query. Many thanks in advance.

Regards


Workbook 1
IDRnnrsDATECOURSETIMENOHORSESpeed Fig Required
1510 June 2015Beverley14004Priory15
1510 June 2015Beverley14006Lolamotion
1510 June 2015Beverley14001Mustnt Grumble

<tbody>
</tbody>



Workbook 2

IDRnnrsDATECOURSETIMENOHORSESPEED
870507 April 2015Lingfield15105Priory0
2,001909 May 2015Goodwood14459Priory80
3,126801 June 2015Windsor17555Priory15

<tbody>
</tbody>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try this:

=VLOOKUP(MAX(IF(Sheet2!$G$2:$G$25="Priory",Sheet2!$C$2:$C$25))&"Priory",CHOOSE({1,2},Sheet2!$C$2:$C$25&Sheet2!$G$2:$G$25,Sheet2!$H$2:$H$25),2,0)

Enter with CTRL+SHFT+ENTR, not just enter. Replace Sheet2 with the tab name of workbook 2.
 
Upvote 0
Try this:

=VLOOKUP(MAX(IF(Sheet2!$G$2:$G$25="Priory",Sheet2!$C$2:$C$25))&"Priory",CHOOSE({1,2},Sheet2!$C$2:$C$25&Sheet2!$G$2:$G$25,Sheet2!$H$2:$H$25),2,0)

Enter with CTRL+SHFT+ENTR, not just enter. Replace Sheet2 with the tab name of workbook 2.

Hi a massive zebra

Many thanks for replying. I have entered your formula into my spreadsheet but unfortunately I am getting the error message Excel ran out of resources etc which probably means my worksheet is too big (currently 120,220 kb) or I am asking excel to do too much. Is there another work around do you think? Again many thanks for your formula which I am sure works perfectly and I am appreciative of your help.

Regards
 
Upvote 0
In H2 of workbook1 enter:
Rich (BB code):

=LOOKUP(9.99999999999999E+307,1/([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2),
     [workbook2.xlsx]Sheet1!$H$2:$H$400)

where G2 house a HORSE (e.g., Priory).
 
Upvote 0
In H2 of workbook1 enter:
Rich (BB code):

=LOOKUP(9.99999999999999E+307,1/([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2),
     [workbook2.xlsx]Sheet1!$H$2:$H$400)

where G2 house a HORSE (e.g., Priory).

Hi Aladin

Many thanks for replying. I am delighted to say that your above solution works an absolute treat and I an extremely grateful for your time and knowledge lent. If I may could I ask if the formula could be adapted so that it would pick up the speed value from the second most recent date? Once again many thanks for your input and above solution it is very much appreciated and I am as ever grateful that such a forum exists with members such as yourself and others whom above and in the past have been generous enough to offer solutions and their time and expertise.

Regards
 
Upvote 0
Thanks for the kind words...

In I2 of workbook1 enter:
Rich (BB code):

=LOOKUP(9.99999999999999E+307,
    1/(([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2)*
        ([workbook2.xlsx]Sheet1!$H$2:$H$400=$H2)),
     [workbook2.xlsx]Sheet1!$H$2:$H$400)

This should pick out the speed associated with the second most recent date.
 
Upvote 0
Thanks for the kind words...

In I2 of workbook1 enter:
Rich (BB code):

=LOOKUP(9.99999999999999E+307,
    1/(([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2)*
        ([workbook2.xlsx]Sheet1!$H$2:$H$400=$H2)),
     [workbook2.xlsx]Sheet1!$H$2:$H$400)

This should pick out the speed associated with the second most recent date.

Hi Aladin

Many thanks again for replying. I have inserted the above formula in my spreadsheet but it seems to be picking up the newest speed figure and not the second newest figure which in this case should have been 80. Any further pointers would be much appreciated.

Regards
 
Upvote 0
Hi Aladin

Many thanks again for replying. I have inserted the above formula in my spreadsheet but it seems to be picking up the newest speed figure and not the second newest figure which in this case should have been 80. Any further pointers would be much appreciated.

Regards

No wonder. We must not test for equality to H2, rather the opposite...
Rich (BB code):

=LOOKUP(9.99999999999999E+307,
   1/(([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2)*
   (1-([workbook2.xlsx]Sheet1!$H$2:$H$400=$H2))),
   [workbook2.xlsx]Sheet1!$H$2:$H$400)
 
Upvote 0
No wonder. We must not test for equality to H2, rather the opposite...
Rich (BB code):

=LOOKUP(9.99999999999999E+307,
   1/(([workbook2.xlsx]Sheet1!$G$2:$G$400=$G2)*
   (1-([workbook2.xlsx]Sheet1!$H$2:$H$400=$H2))),
   [workbook2.xlsx]Sheet1!$H$2:$H$400)

Hi Aladin

Many thanks the above formula works an absolute treat. I hope you dont mind me asking but is it easy enough to adjust to look up 3rd and 4th most recent dates, I would also love to know what the formula is saying so as to give me a better understanding of how it picks out the recentness.

Regards
 
Upvote 0
Hi Aladin

Many thanks the above formula works an absolute treat. I hope you dont mind me asking but is it easy enough to adjust to look up 3rd and 4th most recent dates, I would also love to know what the formula is saying so as to give me a better understanding of how it picks out the recentness.

Regards

We'll need to switch to a different approach though...

workbook2 (the data is extended)

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
IDRnnrsDATECOURSETIMENOHORSESPEED
2​
870​
5​
7-Apr-15​
Lingfield
1510​
5​
Priory
0​
3​
2001​
9​
9-May-15​
Goodwood
1445​
9​
Priory
80​
4​
2200​
7​
15-May-15​
Lingfield
1510​
7​
Priory
60​
5​
2307​
6​
30-May-15​
Blackwood
1230​
8​
Lolamotion
80​
6​
2400​
6​
30-May-15​
London
1330​
7​
Priory
15​
7​
3126​
8​
1-Jun-15​
Windsor
1755​
5​
Priory
15​

workbook1, where we compute the N (4) speed figures corresponding to the most recent race dates of a given horse...

H2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX([workbook2.xlsx]Sheet1!$H$2:$H$7,1/(1/MATCH(LARGE(IF([workbook2.xlsx]Sheet1!$G$2:$G$7=$G2,[workbook2.xlsx]Sheet1!$C$2:$C$7+(1-ROW([workbook2.xlsx]Sheet1!$C$2:$C$7)/10^7)),COLUMNS($H2:H2)),IF([workbook2.xlsx]Sheet1!$G$2:$G$7=$G2,[workbook2.xlsx]Sheet1!$C$2:$C$7+(1-ROW([workbook2.xlsx]Sheet1!$C$2:$C$7)/10^7),0)))),"")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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