vlookup based on 2 criteria (text and most recent date)

[Giancar]

Hi everyone,

Quite difficult to explain with my english skills!

In G4 I need a vlookup formula that:

• vlookup column G (Margin)
• if cells in column E are matching
• if the date is the most recent vs column B

While in H4:

• vlookup column G (Margin)
• if cells in column E are matching
• if cells in column D are matching
• if the date is the most recent vs column B
  
  I have already added on range G4:H6 values I would like to have, as it is the easiest way to let you understand. Thank you in advance

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]DATE[/TD]
[TD]YEAR[/TD]
[TD]MONTH[/TD]
[TD]NAME[/TD]
[TD]MARGIN[/TD]
[TD]PREVIOUS MARGIN[/TD]
[TD]SAME MONTH MARGIN[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]2014-12-11[/TD]
[TD]2014[/TD]
[TD]12[/TD]
[TD]UK[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]2014-12-11[/TD]
[TD]2014[/TD]
[TD]12[/TD]
[TD]USA[/TD]
[TD]2000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]2014-12-11[/TD]
[TD]2014[/TD]
[TD]12[/TD]
[TD]RUSSIA[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10023[/TD]
[TD]2015-05-03[/TD]
[TD]2015[/TD]
[TD]5[/TD]
[TD]USA[/TD]
[TD]1500[/TD]
[TD]2000[/TD]
[TD]7500[/TD]
[/TR]
[TR]
[TD]19343[/TD]
[TD]2015-05-03[/TD]
[TD]2015[/TD]
[TD]5[/TD]
[TD]RUSSIA[/TD]
[TD]2500[/TD]
[TD]3000[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]12123[/TD]
[TD]2015-05-03[/TD]
[TD]2015[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]3500[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]2014-05-15[/TD]
[TD]2014[/TD]
[TD]5[/TD]
[TD]UK[/TD]
[TD]3500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1898[/TD]
[TD]2014-05-15[/TD]
[TD]2014[/TD]
[TD]5[/TD]
[TD]RUSSIA[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7856[/TD]
[TD]2014-05-15[/TD]
[TD]2014[/TD]
[TD]5[/TD]
[TD]USA[/TD]
[TD]7500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Saba Sabaratnam]

Hi Glancar,

I have developed formulas to solve your problem and you can download it from the following link



[TABLE="class: table table-bordered table-striped, width: 100%"]
<tbody style="box-sizing: border-box;">[TR]
[TD="class: responsiveTable, bgcolor: #F8F8F8"]https://sabercathost.com/9pCD/vlookup-based-2-criteria-text-most-recent-date.xlsx[/TD]
[/TR]
</tbody>[/TABLE]

Let me know if it is what you want.

Saba
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: table table-bordered table-striped, width: 100%"]
<tbody style="box-sizing: border-box;">[TR]
[TD="class: responsiveTable, bgcolor: #F8F8F8"]https://sabercathost.com/9pCD/vlookup-based-2-criteria-text-most-recent-date.xlsx[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Fluff]

@Saba Sabaratnam
Could you please post your solution to the thread. As per rule#4
Many thanks

 

[Giancar]

@Saba Sabaratnam
Could you please post your solution to the thread. As per rule#4
Many thanks

I do not see any reply on that link to be honest, or I do not know how to do it

 

[Fluff]

Are you refering to the link in post#2?

 

[Giancar]

Are you refering to the link in post#2?

Yep! I mean that quote. Sorry if I was not clear.

Thank you

 

[Saba Sabaratnam]

Thank you Fluff

Giancar,

Enter the data table in the range A2 to H11

Enter the following array formula into G3 (Previous Margin columns) by pressing Shift + Control + Enter

=IFERROR(INDEX($F$3:$F$11,MATCH(MAX(IF((B3>$B$3:$B$11)*(E3=$E$3:$E$11),$B$3:$B$11,"")),IF((B3>$B$3:$B$11)*(E3=$E$3:$E$11),$B$3:$B$11,""),0)),"")

Enter the following array formula into H3 (Same Month Margin column)

=IFERROR(INDEX($F$3:$F$11,MATCH(MAX(IF((B3>$B$3:$B$11)*(E3=$E$3:$E$11)*(D3=$D$3:$D$11),$B$3:$B$11,"")),IF((B3>$B$3:$B$11)*(E3=$E$3:$E$11)*(D3=$D$3:$D$11),$B$3:$B$11,""),0)),"")

Copy the above formulas down to row 11

Let me know how you go.

Saba

 

[Giancar]

Hi Saba,

It did not work, but I made a Pivot Table and I got a similar result.
Sorry for delay on feedback.

Thanks