i'm trying to do a vlookup to get data from one tab into another in the same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm completely
stumped.

Thanks in advance...

More info along with several examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klp" wrote in message
...
i'm trying to do a vlookup to get data from one tab into another in the
same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how
to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added
together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm
completely
stumped.

Thanks in advance...

OK i will try my best... i didn't give any examples because it's VERY
involved and hard to explain.

Here is what the lookup table looks like in general:

Cust Srv Cust Srv Cust Srv Tech
Tech Tech
North MidWest South North
Midwest South
8/1/07 335 887 117 522
364 914
8/2/07 703 669 350 842
539 973
8/3/07 26 860 387 303
861 276

The spreasheet that needs to get this data looks like this:

Date Division Area Queue Data
8/1/07 NC North Cust Srv <should be 335
8/1/07 NC North Tech <should be 522
8/1/07 NC North Email <no data needed
8/1/07 SC MidWest Cust Srv <should be 887
8/1/07 SC Tech <should be
364 + 914

the same repeats for the next day.

I have separate lines for each division/area/queue because then i take this
info and make a pivot table in order to show roll-ups.

I want the numbers in the "data" column to automatically pull from the
lookuptable somehow - depending on the date and queue/division/area.


"Don Guillett" wrote:

More info along with several examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klp" wrote in message
...
i'm trying to do a vlookup to get data from one tab into another in the
same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how
to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added
together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm
completely
stumped.

Thanks in advance...

With my input on row 1 and output on row 11, both starting in column A then:


in E12:

=SUMPRODUCT(($B$1:$G$1=D12)*($B$2:$G$2=C12)*($A$3: $A$5=A12)*($B$3:$G$5))

Copy down

I didn't understand your last output of 364+914 nut hopefully the above will
help


Cust Srv Cust Srv Cust Srv Tech Tech Tech <== row 1
North MidWest South North MidWest South
08/01/2007 335 887 117 522 364 914
08/02/2007 703 669 350 842 539 973
08/03/2007 26 860 387 303 861 276





Date Division Area Queue <== row 11
08/01/2007 NC North Cust Srv 335
08/01/2007 NC North Tech 522
08/01/2007 NC North Email 0
08/01/2007 SC MidWest Cust Srv 887
08/01/2007 SC MidWest Tech 364


"klp" wrote:

OK i will try my best... i didn't give any examples because it's VERY
involved and hard to explain.

Here is what the lookup table looks like in general:

Cust Srv Cust Srv Cust Srv Tech
Tech Tech
North MidWest South North
Midwest South
8/1/07 335 887 117 522
364 914
8/2/07 703 669 350 842
539 973
8/3/07 26 860 387 303
861 276

The spreasheet that needs to get this data looks like this:

Date Division Area Queue Data
8/1/07 NC North Cust Srv <should be 335
8/1/07 NC North Tech <should be 522
8/1/07 NC North Email <no data needed
8/1/07 SC MidWest Cust Srv <should be 887
8/1/07 SC Tech <should be
364 + 914

the same repeats for the next day.

I have separate lines for each division/area/queue because then i take this
info and make a pivot table in order to show roll-ups.

I want the numbers in the "data" column to automatically pull from the
lookuptable somehow - depending on the date and queue/division/area.


"Don Guillett" wrote:

More info along with several examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klp" wrote in message
...
i'm trying to do a vlookup to get data from one tab into another in the
same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how
to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added
together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm
completely
stumped.

Thanks in advance...

i can get your example to work when i paste it into a spreadsheet, but i
can't get it to work in mine. can you look at my fromula and see what i'm
missing?

=SUMPRODUCT((Numbers!$C$39:'Phone Stats
Data'!$J$39=G2)*(Numbers!$C$40:$J$40=E2)*(Numbers! $B$41:$B$207=A2)*(Numbers!$F$41:$J$207))

i've never used a SUMPRODUCT formula before so this is all new to me.
is it ok that it's on a different tab?

Also, is there some way to adjust it so that instead of showing "0" for
email, it stays blank or shows "N/A". I don't want it to calculate 0's in my
pivot table.

Thanks for your help, i think this will solve my problem if i can get it to
work.

~klp



"Toppers" wrote:

With my input on row 1 and output on row 11, both starting in column A then:


in E12:

=SUMPRODUCT(($B$1:$G$1=D12)*($B$2:$G$2=C12)*($A$3: $A$5=A12)*($B$3:$G$5))

Copy down

I didn't understand your last output of 364+914 nut hopefully the above will
help


Cust Srv Cust Srv Cust Srv Tech Tech Tech <== row 1
North MidWest South North MidWest South
08/01/2007 335 887 117 522 364 914
08/02/2007 703 669 350 842 539 973
08/03/2007 26 860 387 303 861 276





Date Division Area Queue <== row 11
08/01/2007 NC North Cust Srv 335
08/01/2007 NC North Tech 522
08/01/2007 NC North Email 0
08/01/2007 SC MidWest Cust Srv 887
08/01/2007 SC MidWest Tech 364


"klp" wrote:

OK i will try my best... i didn't give any examples because it's VERY
involved and hard to explain.

Here is what the lookup table looks like in general:

Cust Srv Cust Srv Cust Srv Tech
Tech Tech
North MidWest South North
Midwest South
8/1/07 335 887 117 522
364 914
8/2/07 703 669 350 842
539 973
8/3/07 26 860 387 303
861 276

The spreasheet that needs to get this data looks like this:

Date Division Area Queue Data
8/1/07 NC North Cust Srv <should be 335
8/1/07 NC North Tech <should be 522
8/1/07 NC North Email <no data needed
8/1/07 SC MidWest Cust Srv <should be 887
8/1/07 SC Tech <should be
364 + 914

the same repeats for the next day.

I have separate lines for each division/area/queue because then i take this
info and make a pivot table in order to show roll-ups.

I want the numbers in the "data" column to automatically pull from the
lookuptable somehow - depending on the date and queue/division/area.


"Don Guillett" wrote:

More info along with several examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klp" wrote in message
...
i'm trying to do a vlookup to get data from one tab into another in the
same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how
to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added
together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm
completely
stumped.

Thanks in advance...

Don't understand what you are doing here .....You appear to be looking at two
sheets ...

=SUMPRODUCT((Numbers!$C$39:'Phone Stats Data'!$J$39=G2)

Should it be ......?

=SUMPRODUCT((Numbers!$C$39:$J$39=G2)

"klp" wrote:

i can get your example to work when i paste it into a spreadsheet, but i
can't get it to work in mine. can you look at my fromula and see what i'm
missing?

=SUMPRODUCT((Numbers!$C$39:'Phone Stats
Data'!$J$39=G2)*(Numbers!$C$40:$J$40=E2)*(Numbers! $B$41:$B$207=A2)*(Numbers!$F$41:$J$207))

i've never used a SUMPRODUCT formula before so this is all new to me.
is it ok that it's on a different tab?

Also, is there some way to adjust it so that instead of showing "0" for
email, it stays blank or shows "N/A". I don't want it to calculate 0's in my
pivot table.

Thanks for your help, i think this will solve my problem if i can get it to
work.

~klp



"Toppers" wrote:

With my input on row 1 and output on row 11, both starting in column A then:


in E12:

=SUMPRODUCT(($B$1:$G$1=D12)*($B$2:$G$2=C12)*($A$3: $A$5=A12)*($B$3:$G$5))

Copy down

I didn't understand your last output of 364+914 nut hopefully the above will
help


Cust Srv Cust Srv Cust Srv Tech Tech Tech <== row 1
North MidWest South North MidWest South
08/01/2007 335 887 117 522 364 914
08/02/2007 703 669 350 842 539 973
08/03/2007 26 860 387 303 861 276





Date Division Area Queue <== row 11
08/01/2007 NC North Cust Srv 335
08/01/2007 NC North Tech 522
08/01/2007 NC North Email 0
08/01/2007 SC MidWest Cust Srv 887
08/01/2007 SC MidWest Tech 364


"klp" wrote:

OK i will try my best... i didn't give any examples because it's VERY
involved and hard to explain.

Here is what the lookup table looks like in general:

Cust Srv Cust Srv Cust Srv Tech
Tech Tech
North MidWest South North
Midwest South
8/1/07 335 887 117 522
364 914
8/2/07 703 669 350 842
539 973
8/3/07 26 860 387 303
861 276

The spreasheet that needs to get this data looks like this:

Date Division Area Queue Data
8/1/07 NC North Cust Srv <should be 335
8/1/07 NC North Tech <should be 522
8/1/07 NC North Email <no data needed
8/1/07 SC MidWest Cust Srv <should be 887
8/1/07 SC Tech <should be
364 + 914

the same repeats for the next day.

I have separate lines for each division/area/queue because then i take this
info and make a pivot table in order to show roll-ups.

I want the numbers in the "data" column to automatically pull from the
lookuptable somehow - depending on the date and queue/division/area.


"Don Guillett" wrote:

More info along with several examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"klp" wrote in message
...
i'm trying to do a vlookup to get data from one tab into another in the
same
worksheet.

The lookup table is 10 columns wide. The problem i'm running into is how
to
tell the vlookup formula which column to look at in the lookup table?

There are 4 different columns that would determine this info: Date,
Division, Area, and Queue. It gets confusing because sometimes the number
needed is not just one column in the lookup table... it's 2 added
together.
(Again, depending on the data in the 4 columns.)

Does anyone have any suggestions or a bettter way to do this? I'm
completely
stumped.

Thanks in advance...