[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

MS Excel: Lookup First, Last and Second Last Values

Hello,

Seeking your help on some data lookup formulas. Attached is a spreadsheet. I am trying to find the following:

1. Lookup the dataset and return the first purchase date of a given customer
2. Lookup the dataset and return the second last purchase date of a given customer
3. Lookup the dataset and return the last purchase date of a given customer

4. For each of the above dates, also return the name of the sales consultant who the customer was served by.

I've tried a few combinations of min/max and offset/index/match, but I'm a bit out of my depth.

Thanks.
ee-Help.xlsx
0
dabug80
Asked:
dabug80
1 Solution
 
Rgonzo1971Commented:
HI,

pls try

=SMALL(IF(C:C=G5,D:D),1) in H5
=INDEX(E:E,MATCH(G5&H5,C:C&D:D,0)) in I5
=LARGE(IF(C:C=G5,D:D),2) in K5
=+INDEX(E:E,MATCH(G5&K5,C:C&D:D,0)) in L5
=LARGE(IF(C:C=G5,D:D),1) in N5
=INDEX(E:E,MATCH(G5&N5,C:C&D:D,0)) in O5

As array formulas (Ctrl-Shift-Enter)

Regards
ee-HelpV1.xlsx
0
 
dabug80Author Commented:
This is a top notch solution. Thanks so much. I've been trying to figure it out for ages. I'll have to read up on the small and large worksheet functions. Plus I didn't know you could combined match references like that. You've certainly taught me something. You're a champ.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now