Lookup Help

[bflan0524]

i need help creating a formula to do a lookup, on my landing page i have an identifier for a company, however on my data page there may be more than one record for that company that all have the same identifier with different start dates. i need a formula to bring in the last date associated with that record. for example

Landing page
[TABLE="width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Company Name[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]DHG1[/TD]
[TD]Northland Tool[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data Page[TABLE="width: 500"]
<tbody>[TR]
[TD]Identifer[/TD]
[TD]Company Name[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]DHG1[/TD]
[TD]Northland Tool[/TD]
[TD]2/28/2019[/TD]
[/TR]
[TR]
[TD]DHG1[/TD]
[TD]Northland Tool[/TD]
[TD]12/31/2019[/TD]
[/TR]
</tbody>[/TABLE]


on my landing page i want to be able to pull in that 12/31/2019 date for my end date

 

[Scott T]

Book1
	A	B	C
1	Identifier	Company Name	End Date
2	DHG1	Northland Tool	12/31/2019
landing

Cell Formulas
Range	Formula
C2	{=INDEX(data!C2:C3,MATCH(A2&MAXIFS(data!C2:C3,data!A2:A3,A2),data!A2:A3&data!C2:C3,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C
1	Identifer	Company Name	End Date
2	DHG1	Northland Tool	2/28/2019
3	DHG1	Northland Tool	12/31/2019
data

 

[jtakw]

Hi,

Assuming your data page End Date column is in ascending order:

Book1
	A	B	C
1	Identifier	Company Name	End Date
2	DHG1	Northland Tool	1/2/2020
landing

Cell Formulas
Range	Formula
C2	=LOOKUP(1,(data!A2:A6=A2)*(data!C2:C6<>""),data!C2:C6)

Book1
	A	B	C
1	Identifer	Company Name	End Date
2	DHG1	Northland Tool	2/28/2019
3	DHG1	Northland Tool	12/31/2019
4	DHG1	Northland Tool	1/1/2020
5	DHG1	Northland Tool	1/2/2020
6	DHG1	Northland Tool
data

Change/adjust cell references/range, sheet name(s) as needed.