Lookup sheet

B

brianwa

I have a list of data on a sheet that goes in columns like this:
Cust #,Name,invoice#,order#,po#,material desc,qty,sum.

The data is 14k rows deep.

I've tried to create a pivot table from this but there are too man
columns.

How can I go about creating a separate lookup formula on a separat
page so if I type just the customer # it will bring up all of thei
invoice#'s, order# ect. I have a feeling that it will involve a
offset/vlookup but I'm not sure.

Thanks in advance
B
 
F

Frank Kabel

Hi
not a formula but have you considered using 'data - Filter - Special
Filter' for this?
 
M

Max

One way, using OFFSET and MATCH

Assume your list as described is in Sheet1, A2 downwards
[Note: the Cust# is presumed *unique* within the list in col A]

In Sheet2
------------
Assuming the Cust# are in col A, A2 down:

Put in B2:

=IF(ISNA(MATCH($A2,Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$
A:$A,0)-1,COLUMN()-1))

Copy B2 across to H2, then copy down as many rows as there are Cust#s in col
A

Cols B to H will extract the Name,invoice#,order#,po#,material desc,qty,sum

Unmatched Cust# in col A will return blanks [""]

--
Alternatively, perhaps just try Data > Filter > Autofilter on col A in
Sheet1 direct ?
[especially if Cust# is *not* unique]

Albeit only a max of 1000 Cust# will show in the Autofilter drop list (the
limit),
you can click on (Custom ..) in the drop menu, and input the Cust# direct

Debra Dalgleish has some nice coverage on Autofilter at:
http://www.contextures.com/xlautofilter02.html#Limits
 
B

brianwa

Thanks for your help guys

Max, I'm having a little trouble with your formula, but it may have t
do with the absolute references.

I have tried to use the Auto filter (that's how it's set up right now)
My concern is that it's not going to be me that uses this data. It wil
be used by excel beginners. So the simpler I can make it th
better....

B
 
Top