Latest date search vlookup and hlookup at the same time

[eurano]

Hi,
Im looking for help on this. I want to always have the latest date of service from blue range of cells in sheet2 to show in column D in sheet1. In this example red cells in sheet 2 should be in red cells in sheet1. Any help would be appreciated.

 

[gaz_chops]

Welcome to the forum.

Try

=MAX(IF(Sheet2!$B$4:$B$10=$B2,Sheet2!$D$4:$G$10))

Must be entered as an array (Ctrl, Shift & Enter).

 

[eurano]

Welcome to the forum.

Try

=MAX(IF(Sheet2!$B$4:$B$10=$B2,Sheet2!$D$4:$G$10))

Must be entered as an array (Ctrl, Shift & Enter).

Hi,
The result of this formula is 0. I enterred as array.

 

[gaz_chops]

Are the cells H4:?20 blank or zero?

And are they actual dates or text?

 

[eurano]

Are the cells H4:?20 blank or zero?

Yes they are blank but they will be filled in the future.

 

[gaz_chops]

And are they actual dates or text?

 

[eurano]

And are they actual dates or text?

text but if it could be easier I may change it to date. I just want this to work desnt matter what formating it will be.

 

[gaz_chops]

Change them to dates and it should work.

 

[eurano]

Change them to dates and it should work.

It works but it doesnt match because if "serial number" is 4536456 it shows last date for "serial number" 234324324 and it shouldnt

 

[gaz_chops]

It works fine for me! Post the formula as you entered it.

[TABLE="width: 585"]
<!--StartFragment--> <colgroup><col width="65" span="9" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
 [/TR]
 [TR]
  [TD]E15[/TD]
  [TD="align: right"]234324324[/TD]
  [TD][/TD]
  [TD="class: xl63"][/TD]
  [TD="class: xl63"][/TD]
  [TD="class: xl63"][/TD]
  [TD="class: xl63"][/TD]
  [TD="class: xl63, align: right"]12/01/15[/TD]
  [TD="class: xl63, align: right"]25/02/16[/TD]
 [/TR]
 [TR]
  [TD]R14[/TD]
  [TD="align: right"]4536456[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]21/08/15[/TD]
  [TD="class: xl63, align: right"]21/10/15[/TD]
  [TD="class: xl63, align: right"]23/10/15[/TD]
  [TD="class: xl63, align: right"]25/11/15[/TD]
  [TD="class: xl63, align: right"]24/12/15[/TD]
  [TD="class: xl63, align: right"]31/01/16[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]E15[/TD]
  [TD="align: right"]234324324[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]25/02/16[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]R14[/TD]
  [TD="align: right"]4536456[/TD]
  [TD][/TD]
  [TD="class: xl63, align: right"]31/01/16[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]