concatenate and lookup last value

smgomez

New Member
Joined
Nov 13, 2009
Messages
48
I have concatenated two rows in first worksheet and found the first value in the second worksheet

worksheet1
Fiscal YearPeriod
2020A12

=VLOOKUP(CONCATENATE(E2," ",F2),'Time Periods'!A$2:H$215,2,FALSE) this formula gets me to my first Period From value of 8/29/2020 which is great and now in a separate cell I need to get to my last Period To value of 10/2/2020

Time Periods
Yr/PeriodPeriod FromPeriod To
2020A 128/29/20209/4/2020
2020A 129/5/20209/11/2020
2020A 129/12/20209/18/2020
2020A 129/19/20209/25/2020
2020A 129/26/202010/2/2020

Thanks for your help
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,644
By far the easiest way to find the last date is using the MAXIFS-function:
=MAXIFS(Period To Range, Yr/Period Range, E2&" "&F2)

This one returns the max date on the Period To Range where the conditions match.

You could also use similar MINIFS-function to find the earliest date from the Period From range.
 
Solution

smgomez

New Member
Joined
Nov 13, 2009
Messages
48
WOW, so much easier THANKS. I used both MAXIFS and MINIFS to get to the dates needed :)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,886
Members
412,689
Latest member
nhsmedic
Top