Solved

# MS Excel: Lookup First, Last and Second Last Values

Posted on 2015-02-09
50 Views
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
Question by:dabug80

LVL 49

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 40600125
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

LVL 1

Author Closing Comment

ID: 40601917
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.