nested vlookup?

[Guest]

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 Guillett]

More info along with several examples.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

yes, it's two separate tabs.

The data i need is on the "Numbers" tab.

 

[Guest]

I can only repeat what I said in my earlier note: All data being extracted
has to on the same sheet as per the formula below but G2, E2, A2 could be on
a sepaate sheet.

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


Send w/book to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

 

[Guest]

actually, i was able to figure it out.
Thanks so much for the help, this works perfectly!!!!

~klp

 

[Guest]

Well done! It's always better when you solve it yourself.

Thanks for the feedback.