Lookup Help

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
192
Office Version
  1. 2010
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
IdentifierCompany NameEnd Date
DHG1Northland Tool

<tbody>
</tbody>

Data Page
IdentiferCompany NameEnd Date
DHG1Northland Tool2/28/2019
DHG1Northland Tool12/31/2019

<tbody>
</tbody>


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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Book1
ABC
1IdentifierCompany NameEnd Date
2DHG1Northland Tool12/31/2019
landing
Cell Formulas
RangeFormula
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
ABC
1IdentiferCompany NameEnd Date
2DHG1Northland Tool2/28/2019
3DHG1Northland Tool12/31/2019
data
 
Upvote 0
Hi,

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


Book1
ABC
1IdentifierCompany NameEnd Date
2DHG1Northland Tool1/2/2020
landing
Cell Formulas
RangeFormula
C2=LOOKUP(1,(data!A2:A6=A2)*(data!C2:C6<>""),data!C2:C6)



Book1
ABC
1IdentiferCompany NameEnd Date
2DHG1Northland Tool2/28/2019
3DHG1Northland Tool12/31/2019
4DHG1Northland Tool1/1/2020
5DHG1Northland Tool1/2/2020
6DHG1Northland Tool
data


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

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top