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

ID	DATE	YEAR	MONTH	NAME	MARGIN	PREVIOUS MARGIN	SAME MONTH MARGIN
100	2014-12-11	2014	12	UK	1000
101	2014-12-11	2014	12	USA	2000
102	2014-12-11	2014	12	RUSSIA	3000
10023	2015-05-03	2015	5	USA	1500	2000	7500
19343	2015-05-03	2015	5	RUSSIA	2500	3000	1500
12123	2015-05-03	2015	5	UK	2000	1000	3500
89	2014-05-15	2014	5	UK	3500
1898	2014-05-15	2014	5	RUSSIA	1500
7856	2014-05-15	2014	5	USA	7500

<tbody>
</tbody>

 

[Saba Sabaratnam]

Hi Glancar,

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

https://sabercathost.com/9pCD/vlookup-based-2-criteria-text-most-recent-date.xlsx

<tbody style="box-sizing: border-box;">
</tbody>

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;">

https://sabercathost.com/9pCD/vlookup-based-2-criteria-text-most-recent-date.xlsx

<tbody style="box-sizing: border-box;">
</tbody>

</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