Help Vlookup on creating report?

**excelator** · 12-01-2010, 11:42 PM

Hi,
I'm trying to build a report whereas someone can type in store number in A2
and below will generate the following:
UPC Item Description Item Number, sales etc......

These metrics (UPC, Item description Item numbers) are all available in the table I'm referencing it to.

Any suggestions on formula?

thanks in advance.

**KiPA** · 12-02-2010, 04:58 AM

Like this? There's two alternatives because of a bit of hazy definition.

**excelator** · 12-02-2010, 10:52 AM

Thanks this is how I need it to populate. I have gotten this far using the Vlook up function but I'm not getting the formula right as (using your example)
Store 102 will have multiple of entries in column A (in sheet 2 in your example)

So it would list all the items reporting into store 102. My reference sheet has over 8,000 entries as example store 102 may have 30 products (and say there are over 250 stores altogether)

I've been dragging the function all the way to the bottom but getting #N/A's or multiple products

I've highlighted in yellow what I've added.

hope this make sense what I require?

**mahju** · 12-02-2010, 03:52 PM

Vlookup ffunction is for searching one value.
One you drag down the formula it changes to A3, A4 etc. and you get an error message

Use pivot table instead.

See attached worksheet --- Sheet 2

One thing --- You should not enter the duplicate value in one store

i.e all the fields in any two rows should not be same

otherwise sales will be added

In last last column total means sale

The other option is advanced filter that will extract the entries of desired stored No

That will be given later

Regards

**KiPA** · 12-02-2010, 06:39 PM

Yees... pivot table would be good for this. There's no sense to try it with vlookup.

**squiggler47** · 12-03-2010, 04:46 AM

Vlookup wouldnt work too well in this situation, using the pivot table is the easiest and best option.

Otherwise if your store data is in order of stores you could use index/match to pull the values across, also you need to limit the range of your VLOOKUPS, if you are only using 8k rows, dont lookup 65k rows!

**squiggler47** · 12-03-2010, 05:20 AM

I added some Index match formulas and 2 named ranges to cut down on the data!

The formulas assume that your data is sorted by store number, otherwise they become more difficult to handle and complex!

This in mind, i converted your data into a List, to allow sorting of the data to meet this requirement, other than the borders and filters it wont affect anything!