Using vlookup for non-unique first and last values

[siddhu666]

Hi, I have data in the form
Date Bill No.
20 Nov, 11 125
20 Nov, 11 126
20 Nov, 11 127
20 Nov, 11 128
21 Nov, 11 129
21 Nov, 11 130
21 Nov, 11 131

Usually the bill numbers are in ascending order, but sometimes, there may be one missing (cancelled transaction, etc.)
Now I want to make a daily report which tells me how much business was done in a day (which bill numbers were done in a day).

The format is something like:
Date From Bill No. To Bill No.
20 Nov,11 125 128
21 Nov,11 129 131

What formula can I use for the fields "from bill no." and "to bill no."?
vlookup helps with the "from" one. No idea for the "to" one. Any suggestions??

[darkyam]

Say your data is in A1:B30 and your table below is in D1:F10. F1 and down can have =Index(B:B,Match(D2,A:A,0)-1). This works if they are indeed in order (you can easily use sort for this). Alternatively, you can use =SUMPRODUCT(MAX((B$1:B$30)*(A$1:A$30=D1))). That will work if they are out of order.

[siddhu666]

Say your data is in A1:B30 and your table below is in D1:F10. F1 and down can have =Index(B:B,Match(D2,A:A,0)-1). This works if they are indeed in order (you can easily use sort for this). Alternatively, you can use =SUMPRODUCT(MAX((B$1:B$30)*(A$1:A$30=D1))). That will work if they are out of order.

Hey darkyam,
The data is in ascending date wise order. The formula is somehow showing the smallest bill no. of the next date, and not the highest number of the current date. Any idea?

[darkyam]

That shouldn't be the case with either formula. Any chance of posting a dummy workbook?

[Haseeb Avarakkan]

Hello Siddhu,

Use Pivot table & use MIN & MAX values. or use Array Formula.

See attached