Index, Vlookup, Offset

[Guest]

I have the following raw data setup:

Invoice# Date Description Amount
2007-1 4/1/07 Signage 250.00
2007-1 4/1/07 Mugs 115.00
2007-2 4/2/07 Signage 275.00

In a summary sheet:

I would like to pull the first "Date" for "Invoice#" 2007-1 on line 1.
If Invoice# 2007-1 has a second record, I would like to then pull the second
"Date" for "Invoice#" 2007-1 on line 2.

Thank you in advance for your help

 

[T. Valko]

For the first date:

Summary sheet A1 = 2007-1

=VLOOKUP(A1,Sheet1!A210,2,0)

For the second date (note: this formula will return the date corresponding
to the *LAST INSTANCE* of 2007-1. If there are only 2 instances it will
return the second instance. If there are 10 instances it will return the
10th instance)

=LOOKUP(2,1/(Sheet1!A2:A10=A1),Sheet1!B2:B10)

Both formula cells need to be formatted as DATE.

Biff

 

[Roger Govier]

Hi

Take a look at Debra Dalgleish's site for Advanced Filter, extracting
data to another sheet.
http://www.contextures.com/xladvfilter01.html#ExtractWs

 

[Guest]

Found a solution that would work in discussion history. Thank you to those
that replied to the post.



Perhaps this could be an option ..

Source data in Sheet1's cols A and B, from row2 down

In Sheet2,
Assume Store # will be input in B1

In A2:
=IF(Sheet1!A2="","",IF(Sheet1!A2=$B$1,ROW(),""))
Leave A1 empty

In B2:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(Sheet1!B:B,SMALL(A:A,ROW(A1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B returns the required results all neatly bunched at the
top.