Lookup fist date based on criteria

[SimonHughes]

Hello, I have multiple transactions to a customer and want to return the first and last transaction dates. The data table is on one tab "Table" and the returns on another "Data", the primary data has dates in column A (nemce the range and customer name in column B. I have tried this formula for the first date which returns a #VALUE error; =MIN(IF(Table!$B$2:$B$130,Data!H4,Table!$A$2:$A$130)) I have tried this as an array formula and standard formula but neither works.

I can get the fist date by using a LOOKUP function and the last date with the VLOOKUP function but would like a better solution than this.

Any suggestions appreciated.

Using Excel 2016

 

[arthurbr]

Did you commit your formula with Ctrl+Shift+Enter properly ? ( insertion point must be in the formula bar)
Otherwise you can use the AGGREGATE function for the first and last date
I'm sorry I can't help more, I'm out of time

 

[Alex Blakenburg]

You might want to update your profile to show which version of Excel you are running.
If you have at least Excel 2016, this might work for you.

20210708 MinIfs MaxIfs.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Dates	Cust						Cust	Min	Max
2	1/07/2021	abcd						abcd	1/07/2021	13/07/2021
3	1/07/2021	abcd
4	1/07/2021	abcd
5	8/07/2021	abcd
6	9/07/2021	abcd
7	10/07/2021	abcd
8	11/07/2021	abcd
9	12/07/2021	abcd
10	13/07/2021	abcd
11	1/07/2021	next cust
12	8/07/2021	next cust
13	9/07/2021	next cust
14	13/07/2021	next cust
15	13/07/2021	next cust
16
Sheet1

Cell Formulas
Range	Formula
I2	I2	=MINIFS($A$2:$A$130,$B$2:$B$130,$H2)
J2	J2	=MAXIFS($A$2:$A$130,$B$2:$B$130,$H2)

 

[Fluff]

@Alex Blakenburg the minifs & maxifs are only available in 2019 & 365, not 2016 which the OP is using.

@SimonHughes are your dates real dates rather than text?

 

[Alex Blakenburg]

@Alex Blakenburg the minifs & maxifs are only available in 2019 & 365, not 2016 which the OP is using.

@Fluff, Thanks, I made the mistake of taking the first result that Google offered me.
Lesson learnt, will make sure I try for a Microsoft webpage where possible.

 

[SimonHughes]

@Alex Blakenburg the minifs & maxifs are only available in 2019 & 365, not 2016 which the OP is using.

@SimonHughes are your dates real dates rather than text?

Hi, yes, the dates are real dates

 

[SimonHughes]

@Fluff, Thanks, I made the mistake of taking the first result that Google offered me.
Lesson learnt, will make sure I try for a Microsoft webpage where possible.

View attachment 42432

Not to worry, and thanks for taking the time to help

 

[SimonHughes]

Did you commit your formula with Ctrl+Shift+Enter properly ? ( insertion point must be in the formula bar)
Otherwise you can use the AGGREGATE function for the first and last date
I'm sorry I can't help more, I'm out of time

Yes, the formula was entered correctly. Cannot work out how to use the Aggregate function for this one - puzzled.

 

[Fluff]

In that case can you some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.