OFFSET function + VLOOKUP together in Excel 97? 
Author Message
 OFFSET function + VLOOKUP together in Excel 97?

Greetings, all!

I have a database of names and figures.  The names all have duplicates
corresponding to categories listed beside them in the next column.
There are 15 categories, so there are fiften listings of each name.

What I have basically is this:

Same Name     |     product1     |     3
Same Name     |     product2     |     3
Same Name     |     product3     |     2
Same Name     |     product4     |     3
Same Name     |     product5     |     7
Same Name     |     product6     |     3
Same Name     |     product7     |    11
Same Name     |     product8     |     8
Another Name  |     product1     |     3
Another Name  |     product2     |    17
Another Name  |     product3     |     2
Another Name  |     product4     |     6
Another Name  |     product5     |     1

and so on...

I am trying to pull the data for selected products into other
spreasheets dedicated to each name.

VLOOKUP will return the first match, as:
=VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

I need additional figures for other select products and have tried
using the OFFSET function to pull them.  For example, using:

=OFFSET(VLOOKUP("Same
Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,2,1,1)

to try reading the product3 statistic for "Same Name" individual, but
it doesn't work -- Excel 97 does not accept the formula.

Any ideas to make this work in Excel or will I have to try my hand at
using MS Access?

Thanks in advance...



Mon, 17 Mar 2003 10:57:12 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
If I understand correctly, you can use an array formula:

Assuming row 1 contains the criteria that you want to match up against
the rest of the range, and range A2:C10001 contains the lookup data:

  A           B       C
1 Same Name   product (formula here)
2 lookup table begins in row 2
3

In C1 put the following formula:

{=SUM(($A$1=$A$2:$A$10000)*($B$1=$B$2:$B$10000)*($C$2:$C$10000))}

array-entered (Ctrl + Shift + Enter)

HTH,
Tim


Quote:

> Greetings, all!

> I have a database of names and figures.  The names all have duplicates
> corresponding to categories listed beside them in the next column.
> There are 15 categories, so there are fiften listings of each name.

> What I have basically is this:

> Same Name     |     product1     |     3
> Same Name     |     product2     |     3
> Same Name     |     product3     |     2
> Same Name     |     product4     |     3
> Same Name     |     product5     |     7
> Same Name     |     product6     |     3
> Same Name     |     product7     |    11
> Same Name     |     product8     |     8
> Another Name  |     product1     |     3
> Another Name  |     product2     |    17
> Another Name  |     product3     |     2
> Another Name  |     product4     |     6
> Another Name  |     product5     |     1

> and so on...

> I am trying to pull the data for selected products into other
> spreasheets dedicated to each name.

> VLOOKUP will return the first match, as:
> =VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

> I need additional figures for other select products and have tried
> using the OFFSET function to pull them.  For example, using:

> =OFFSET(VLOOKUP("Same
> Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,2,1,1)

> to try reading the product3 statistic for "Same Name" individual, but
> it doesn't work -- Excel 97 does not accept the formula.

> Any ideas to make this work in Excel or will I have to try my hand at
> using MS Access?

> Thanks in advance...

Sent via Deja.com http://www.deja.com/
Before you buy.


Mon, 17 Mar 2003 13:04:21 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
I didn't describe what I need to do clearly enough.

Illustrated another way, I have a table that looks like this:

NAME                    ITEM         QTY
Same Name     |     Item1     |     3
Same Name     |     Item2     |     3
Same Name     |     Item3     |     2
Same Name     |     Item4     |     3
Same Name     |     Item5     |     7
Same Name     |     Item6     |     3
Same Name     |     Item7     |    11
Same Name     |     Item8     |     8
Another Name  |     Item1     |     3
Another Name  |     Item2     |    17
Another Name  |     Item3     |     2
Another Name  |     Item4     |     6
Another Name  |     Item5     |     1
and so on...

The name field actually has each name appear 16 consecutive times.
The item field has 16 items in each case which repeat for each
different name.  The quantify field indicates how much of each item in
the same row that name sold.

This table has over 11,000 rows of (16 rows for each name with items
and quantities that repeat for each name).  

From a summary sheet in another workbook, I need to display (and be
able to print) each item and how many of each item that each person
sold.

A VLOOKUP will get me the quantify of the first item if I use:
=VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

But how to get some the remaining 15 items?  I tried using the OFFSET
function to pick the quantities out for other items selected out of
the 16-item list for each name but Excel 97 apparently doesn't accept
the use of VLOOKUP as a reference argument for OFFSET.

To pull the QTY of Item3 for SameName, for example, I tried:

=OFFSET(VLOOKUP("SameName",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,2,1,1)

but it didn't work.  



Quote:
>If I understand correctly, you can use an array formula:

>Assuming row 1 contains the criteria that you want to match up against
>the rest of the range, and range A2:C10001 contains the lookup data:

>  A           B       C
>1 Same Name   product (formula here)
>2 lookup table begins in row 2
>3

>In C1 put the following formula:

>{=SUM(($A$1=$A$2:$A$10000)*($B$1=$B$2:$B$10000)*($C$2:$C$10000))}

>array-entered (Ctrl + Shift + Enter)

>HTH,
>Tim



>> Greetings, all!

>> I have a database of names and figures.  The names all have duplicates
>> corresponding to categories listed beside them in the next column.
>> There are 15 categories, so there are fiften listings of each name.

>> What I have basically is this:

>> Same Name     |     product1     |     3
>> Same Name     |     product2     |     3
>> Same Name     |     product3     |     2
>> Same Name     |     product4     |     3
>> Same Name     |     product5     |     7
>> Same Name     |     product6     |     3
>> Same Name     |     product7     |    11
>> Same Name     |     product8     |     8
>> Another Name  |     product1     |     3
>> Another Name  |     product2     |    17
>> Another Name  |     product3     |     2
>> Another Name  |     product4     |     6
>> Another Name  |     product5     |     1

>> and so on...

>> I am trying to pull the data for selected products into other
>> spreasheets dedicated to each name.

>> VLOOKUP will return the first match, as:
>> =VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

>> I need additional figures for other select products and have tried
>> using the OFFSET function to pull them.  For example, using:

>> =OFFSET(VLOOKUP("Same
>> Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,2,1,1)

>> to try reading the product3 statistic for "Same Name" individual, but
>> it doesn't work -- Excel 97 does not accept the formula.

>> Any ideas to make this work in Excel or will I have to try my hand at
>> using MS Access?

>> Thanks in advance...

>Sent via Deja.com http://www.deja.com/
>Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
From your description of the problem, a PivotTable would be what the
doctor ordered!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--


Quote:
> Greetings, all!

> I have a database of names and figures.  The names all have duplicates
> corresponding to categories listed beside them in the next column.
> There are 15 categories, so there are fiften listings of each name.

> What I have basically is this:

> Same Name     |     product1     |     3
[snip]
> and so on...

> I am trying to pull the data for selected products into other
> spreasheets dedicated to each name.

> VLOOKUP will return the first match, as:
> =VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

[snip]


Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?

=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet1
!$A$1:$A$12000,0)-1,1)
Gets the first Item
=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet1
!$A$1:$A$12000,0)-1,2)
Gets the first Item Qty

=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet1
!$A$1:$A$12000,0)-0,1)
Gets the Second Item
=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet1
!$A$1:$A$12000,0)-0,2)
Gets the Second Item Item Qty

the -1, then -0 at the end of argument 2
would progress as -1, -0, +1, +2, +3, . . . +14

Regards,
Tom Ogilvy
MVP Excel


Quote:
> I didn't describe what I need to do clearly enough.

> Illustrated another way, I have a table that looks like this:

> NAME                    ITEM         QTY
> Same Name     |     Item1     |     3
> Same Name     |     Item2     |     3
> Same Name     |     Item3     |     2
> Same Name     |     Item4     |     3
> Same Name     |     Item5     |     7
> Same Name     |     Item6     |     3
> Same Name     |     Item7     |    11
> Same Name     |     Item8     |     8
> Another Name  |     Item1     |     3
> Another Name  |     Item2     |    17
> Another Name  |     Item3     |     2
> Another Name  |     Item4     |     6
> Another Name  |     Item5     |     1
> and so on...

> The name field actually has each name appear 16 consecutive times.
> The item field has 16 items in each case which repeat for each
> different name.  The quantify field indicates how much of each item in
> the same row that name sold.

> This table has over 11,000 rows of (16 rows for each name with items
> and quantities that repeat for each name).

> From a summary sheet in another workbook, I need to display (and be
> able to print) each item and how many of each item that each person
> sold.

> A VLOOKUP will get me the quantify of the first item if I use:
> =VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

> But how to get some the remaining 15 items?  I tried using the OFFSET
> function to pick the quantities out for other items selected out of
> the 16-item list for each name but Excel 97 apparently doesn't accept
> the use of VLOOKUP as a reference argument for OFFSET.

> To pull the QTY of Item3 for SameName, for example, I tried:

=OFFSET(VLOOKUP("SameName",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,
2,1,1)

- Show quoted text -

Quote:

> but it didn't work.



> >If I understand correctly, you can use an array formula:

> >Assuming row 1 contains the criteria that you want to match up against
> >the rest of the range, and range A2:C10001 contains the lookup data:

> >  A           B       C
> >1 Same Name   product (formula here)
> >2 lookup table begins in row 2
> >3

> >In C1 put the following formula:

> >{=SUM(($A$1=$A$2:$A$10000)*($B$1=$B$2:$B$10000)*($C$2:$C$10000))}

> >array-entered (Ctrl + Shift + Enter)

> >HTH,
> >Tim



> >> Greetings, all!

> >> I have a database of names and figures.  The names all have duplicates
> >> corresponding to categories listed beside them in the next column.
> >> There are 15 categories, so there are fiften listings of each name.

> >> What I have basically is this:

> >> Same Name     |     product1     |     3
> >> Same Name     |     product2     |     3
> >> Same Name     |     product3     |     2
> >> Same Name     |     product4     |     3
> >> Same Name     |     product5     |     7
> >> Same Name     |     product6     |     3
> >> Same Name     |     product7     |    11
> >> Same Name     |     product8     |     8
> >> Another Name  |     product1     |     3
> >> Another Name  |     product2     |    17
> >> Another Name  |     product3     |     2
> >> Another Name  |     product4     |     6
> >> Another Name  |     product5     |     1

> >> and so on...

> >> I am trying to pull the data for selected products into other
> >> spreasheets dedicated to each name.

> >> VLOOKUP will return the first match, as:
> >> =VLOOKUP("Same Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE)

> >> I need additional figures for other select products and have tried
> >> using the OFFSET function to pull them.  For example, using:

> >> =OFFSET(VLOOKUP("Same
> >> Name",'[MySpreadsheet.xls]Sheet1'!A1:C10000,3,FALSE),2,2,1,1)

> >> to try reading the product3 statistic for "Same Name" individual, but
> >> it doesn't work -- Excel 97 does not accept the formula.

> >> Any ideas to make this work in Excel or will I have to try my hand at
> >> using MS Access?

> >> Thanks in advance...

> >Sent via Deja.com http://www.deja.com/
> >Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?


Quote:
> I didn't describe what I need to do clearly enough.

> Illustrated another way, I have a table that looks like this:

> NAME                    ITEM         QTY
> Same Name     |     Item1     |     3
> and so on...

> The name field actually has each name appear 16 consecutive times.
> The item field has 16 items in each case which repeat for each
> different name.  The quantify field indicates how much of each item in
> the same row that name sold.
> This table has over 11,000 rows of (16 rows for each name with items
> and quantities that repeat for each name).
> From a summary sheet in another workbook, I need to display (and be
> able to print) each item and how many of each item that each person
> sold.

I am no MVP but,
It seems to me that your task would be much easier if instead of one huge
list you used six{*filter*} worksheets (one for each product), in the workbook and
then listed the 700? sales people against the sale of that product. In other
words change your method of data ENTRY.
Using a simple datasort will give you who is your best salesman - and the
worst! Another sheet which references and totals the other six{*filter*} can give
you product totals for profit/loss and ordering purposes.
HTH
David H.


Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
On Thu, 28 Sep 2000 12:55:37 -0400, "Tom Ogilvy"

Quote:

>=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet1
>!$A$1:$A$12000,0)-1,1)

Tom, I really appreciate the time you put into this problem of mine.
You have awakened me to the MATCH function, although so far I have
failed to grasp what should be put into location $B$19.  Also this
formula apparently sends the items (quantities, too, if I understand
this correctly) into separate dedicated worksheets when the formula
cell is dragged down...  I would have an additional 200 worksheets or
so, one for each name with its accompanying 15 items and quantities...

I really would like to understand this formula a little better.  The
biggest piece of the puzzle is the reference to $B$19.  I understand
where the column B reference is coming from, but is the row 19 the
bottom row of the first group of items as a point of reference?

Thanks again for sharing your expertise with one who is so ignorant...



Tue, 18 Mar 2003 13:47:56 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
David H, I agree totally with your logic here.  What I ended up doing
was doing something very similar to your suggestion and make up
separate workbooks for each product.  I also had to break them down
into ranges (by month, since this is for the entire year to date and
the monthly figures for each product and each salesperson are what is
needed).  A few MS Access queries were all that was needed to
accomplish this.  From there, I performed simple VLOOKUPs in each
range for each name from each page of product.

As each sales agent hadn't sold every product in every month, I also
got introduced to the IS functions, which proved very useful indeed.

My jaw dropped when I initially saw the size of this file I was told
to work with, but after I saw the pattern (redundancy) to it, it was
apparent it could be made much better organized.

Thanks for your thoughts!  This is a terrific newsgroup!

Quote:



>> I didn't describe what I need to do clearly enough.

>> Illustrated another way, I have a table that looks like this:

>> NAME                    ITEM         QTY
>> Same Name     |     Item1     |     3
>> and so on...

>> The name field actually has each name appear 16 consecutive times.
>> The item field has 16 items in each case which repeat for each
>> different name.  The quantify field indicates how much of each item in
>> the same row that name sold.
>> This table has over 11,000 rows of (16 rows for each name with items
>> and quantities that repeat for each name).
>> From a summary sheet in another workbook, I need to display (and be
>> able to print) each item and how many of each item that each person
>> sold.

>I am no MVP but,
>It seems to me that your task would be much easier if instead of one huge
>list you used six{*filter*} worksheets (one for each product), in the workbook and
>then listed the 700? sales people against the sale of that product. In other
>words change your method of data ENTRY.
>Using a simple datasort will give you who is your best salesman - and the
>worst! Another sheet which references and totals the other six{*filter*} can give
>you product totals for profit/loss and ordering purposes.
>HTH
>David H.



Tue, 18 Mar 2003 14:06:34 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
I wonder, can I use VLOOKUPS from a Pivot Table?  I haven't really
worked with them.  

I'll look into the prospect!  Thank you!

On Thu, 28 Sep 2000 12:41:32 -0400, Tushar Mehta

Quote:

>From your description of the problem, a PivotTable would be what the
>doctor ordered!

>--
>Regards,

>Tushar Mehta
>www.tushar-mehta.com



Tue, 18 Mar 2003 14:08:18 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
The idea behind using the PivotTable is that you get what you want --
sans further lookups.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--


Quote:
> I wonder, can I use VLOOKUPS from a Pivot Table?  I haven't really
> worked with them.  

> I'll look into the prospect!  Thank you!

> On Thu, 28 Sep 2000 12:41:32 -0400, Tushar Mehta

> >From your description of the problem, a PivotTable would be what the
> >doctor ordered!

> >--
> >Regards,

> >Tushar Mehta
> >www.tushar-mehta.com



Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?

B19 holds the value "same name"

It is the name you are looking up.

Regards,
Tom Ogilvy


Quote:
> On Thu, 28 Sep 2000 12:55:37 -0400, "Tom Ogilvy"

>=OFFSET([myspreadsheet.xls]Sheet1!$A$1,MATCH($B$19,[myspreadsheet.xls]Sheet
1
> >!$A$1:$A$12000,0)-1,1)

> Tom, I really appreciate the time you put into this problem of mine.
> You have awakened me to the MATCH function, although so far I have
> failed to grasp what should be put into location $B$19.  Also this
> formula apparently sends the items (quantities, too, if I understand
> this correctly) into separate dedicated worksheets when the formula
> cell is dragged down...  I would have an additional 200 worksheets or
> so, one for each name with its accompanying 15 items and quantities...

> I really would like to understand this formula a little better.  The
> biggest piece of the puzzle is the reference to $B$19.  I understand
> where the column B reference is coming from, but is the row 19 the
> bottom row of the first group of items as a point of reference?

> Thanks again for sharing your expertise with one who is so ignorant...



Wed, 18 Jun 1902 08:00:00 GMT  
 OFFSET function + VLOOKUP together in Excel 97?
Seems like a lot of unnecessary work to me - especially since this would
have to be done repeatedly as new data came in.

Just my opinion.  But it looks like meesta is happy with whatever solution
he came up with.

Regards,
Tom Ogilvy



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. Help on vlookup / offset function

2. Nested offset-Vlookup function

3. Problems using RIGHT and VLOOKUP functions together

4. very tough excel question:(vlookup, match, offset)

5. Microsoft Excel OFFSET function

6. sumproduct, offset, and column together

7. Access 97 to Excel - Data Offset

8. Using MIN function nested within the OFFSET function

9. Functions in a function ...MATCH/OFFSET problem

10. ADO and Excel 97 - can they work together?


 
Powered by phpBB® Forum Software © phpBB Group