multiple Vlookups?

I have an issue with excel right now and I was hoping someone could help me here.

I have a worksheet:

Please Login or Register  to view this content.

can anyone help me out with this? I'm confused because I don't know how to do an incremental vlookup.

Originally Posted by Special-K

Rather than say "I have this" and "the output I want is this" can you define what rules you are following to produce the "output"?

sure. I have an array of purchases by customer listed one at a time. I want to turn that array horizontal listed by customer so that each customer is on a different row and each purchase is to the right of that customer.

1: Insert a column at "B", title it "Customer events" or sim.
2: into B2, put this:=COUNTIF(A$2:A2,A2) and copy down - this gives you... (what it describes)
3: Create a pivot table of your data (Data ->pivot blah->finish)
4: Drag customer# to the "row fields" section
5: Drag customer events to "column fields" section
6: Drag purchase amount to data section
7: Drag paid? to underneath customer events button (although I prefer it to the right of the customer# button)

See attached.

If you want to use the "paid?" better in a pivot table environment, you could think about replacing Y and N with 0 and 1. This would also allow you to add calculated fields to the pivot table using this info.

HTH

Originally Posted by Cheeky Charlie

1: Insert a column at "B", title it "Customer events" or sim.
2: into B2, put this:=COUNTIF(A$2:A2,A2) and copy down - this gives you... (what it describes)
3: Create a pivot table of your data (Data ->pivot blah->finish)
4: Drag customer# to the "row fields" section
5: Drag customer events to "column fields" section
6: Drag purchase amount to data section
7: Drag paid? to underneath customer events button (although I prefer it to the right of the customer# button)

See attached.

If you want to use the "paid?" better in a pivot table environment, you could think about replacing Y and N with 0 and 1. This would also allow you to add calculated fields to the pivot table using this info.

HTH

this looks great but it's not exactly what i'm looking for... the paid or not option doesn't need to be organized into separate columns, in fact it can't be separated into different collumns. Also, the output spreadsheet can't be a pivot table because it's actually in a master spreadsheet with a lot of the other customer information (name, location, etc.) i just need to list out their purchases to the right of that. No customer has more than 18 purchases, most have 2-3 purchases. Since the master spreadsheet is already conforming to a certain order, I think i either need to do a lookup to the second array or maybe use vb?

Please Login or Register  to view this content.

Looks like different columns to me.

You can achieve this by incorporating a binary equivalent to Y,N as shown in attachment and as alluded to in my first post. The pivot table can be extracted to any document with simple =(pivottablesheet!cellref) type formulae.

The formulae as shown on Sheet3, or similar, can be copied/extended in whatever GUI (ghastly user interface) you have.

A pivot table is robust, fast and accurate. It does what you asked as I have shown. I am not confident that ridiculously complex nesting match/index or lookup formulae would. If you want any help, feel free to reject a solution, but not a methodology.

HTH