extend index/match

**want2learn** · 01-09-2011, 10:43 AM

I am trying to create a billing statement based on customer sales data, the named range SalesDayBook. I have managed the first entry using Index/Match but need to get subsequent entries if they exist. My formula will only get the first entry, any suggestions would be appreciated.

**Cutter** · 01-09-2011, 11:01 AM

My approach would be to insert a column to left of your SalesDayBook range

In the new column, cell A6 place this formula:

=IF(C6='Billing Statement'!$E$7,1,"")

In A7 place this formula and copy down:

=IF(C7='Billing Statement'!$E$7,COUNT(A$6:A6)+1,"")

You can now do VLOOKUP() formulas within your statement to find the 1,2 & 3 using a check on the number of times Joe Blogg appears by using a COUNTIF() function

**want2learn** · 01-09-2011, 11:34 AM

I was trying to use COUNTIF within the formula, but adding the column works.

This is the formula that I have used to get the actual data after adding your suggestions.

=IF(ISERROR(VLOOKUP(ROW(A17)-16,Sales!$A$5:$H$17,4,0)),"",VLOOKUP(ROW(A17)-16,Sales!$A$5:$H$17,4,0))

I'll mark this solved later, just in case any other suggestions are forthcoming

Thanks Cutter

**Marcol** · 01-09-2011, 12:55 PM

Try this workbook

I'm not sure about your sub totals, best check them.

**Cutter** · 01-09-2011, 01:43 PM

want2learn

This is what I had in mind for your date column on the statement sheet (and dragged down)

=IF(COUNTIF(SalesDayBook,$E$7)>ROWS(A$17:A17)-1,VLOOKUP(ROWS(A$17:A17),SalesDayBook,4,0),"")

**want2learn** · 01-09-2011, 02:40 PM

Marcol

I've used a template that I downloaded, hadn't checked the subtotals yet. Thanks for the input.

Cutter

That's similar to what I was working on, but I kept getting an error. The formula that I posted works, changing the reference columns, but I'll check yours out.

**want2learn** · 01-10-2011, 01:46 PM

Hi Cutter

With some slight alterations I got your formula to work, I had to change the CountIf to only use one column of the table, not all of it. Thanks for your help