I have a pivot table of data that I would like to complete a lookup on. The
data is in a pivot table because I need some of the data grouped before I
complete the lookup on it. I am trying to complete a lookup that first finds
the Center then looks for the Client. Once the correct Center and Client are
found, then I want it to look up the Month and return the data point. So if
I have it lookup Center2, Client4 for Mar., it would return the value of 48.
See example table below:

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 22 3 25
3 Client 2 131 108 107
4 Client 3 4 0 0
5 Client 4 25 24 24
6 Client 5 1 0 7
7 Center1 Total 161 135 138
8 Center2 Client 1 0 0 0
9 Client 2 20 4 4
10 Client 3 0 1 6
11 Client 4 36 32 48
12 Client 5 11 1 2
13 Center2 Total 67 38 60

Any ideas on how to set up the lookup would be greatly appreciated.

This may not be the best approach, but I'd do one of two things...

Option 1.

Add another column to the raw data and build a pivottable using that as the only
row field. This additional field would concatenate the center and client.

=a2&"--"&b2
(and drag down)

Then I could use something like this:
=vlookup(x9&"--"&y9,....
Where x9 held the center and y9 held the client.


Option 2.

Convert the existing pivottable to values (or create a copy of just values on a
different sheet). Then fill all those empty cells with the value from above.

Debra Dalgleish shares a few techniques here (manual and macro):

http://contextures.com/xlDataEntry02.html
and a video
http://www.contextures.com/xlVideos01.html#FillBlanks

Then use another formula to retrieve the value you want.

=sumproduct(--(sheet99!A1:A10="center1"),--(sheet99!b1:b10="client1"),(c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Some added info that I've saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

HowardM wrote:

I have a pivot table of data that I would like to complete a lookup on. The
data is in a pivot table because I need some of the data grouped before I
complete the lookup on it. I am trying to complete a lookup that first finds
the Center then looks for the Client. Once the correct Center and Client are
found, then I want it to look up the Month and return the data point. So if
I have it lookup Center2, Client4 for Mar., it would return the value of 48.
See example table below:

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 22 3 25
3 Client 2 131 108 107
4 Client 3 4 0 0
5 Client 4 25 24 24
6 Client 5 1 0 7
7 Center1 Total 161 135 138
8 Center2 Client 1 0 0 0
9 Client 2 20 4 4
10 Client 3 0 1 6
11 Client 4 36 32 48
12 Client 5 11 1 2
13 Center2 Total 67 38 60

Any ideas on how to set up the lookup would be greatly appreciated.

--

Dave Peterson

ps. I think option 1 is easier.

<<snipped

Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2

I'm guessing how your table is setup.

Assuming that the clients are the same and in the same sequence for each
center.

Lookup values:

G1 = Center1
H1 = Client 3
I1 = Mar

=INDEX(C2:E13,MATCH(G1,A2:A12,0)+MATCH(H1,B2:B6,0)-1,MATCH(I1,C1:E1,0))

--
Biff
Microsoft Excel MVP


"HowardM" wrote in message
...
I have a pivot table of data that I would like to complete a lookup on.
The
data is in a pivot table because I need some of the data grouped before I
complete the lookup on it. I am trying to complete a lookup that first
finds
the Center then looks for the Client. Once the correct Center and Client
are
found, then I want it to look up the Month and return the data point. So
if
I have it lookup Center2, Client4 for Mar., it would return the value of
48.
See example table below:

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 22 3 25
3 Client 2 131 108 107
4 Client 3 4 0 0
5 Client 4 25 24 24
6 Client 5 1 0 7
7 Center1 Total 161 135 138
8 Center2 Client 1 0 0 0
9 Client 2 20 4 4
10 Client 3 0 1 6
11 Client 4 36 32 48
12 Client 5 11 1 2
13 Center2 Total 67 38 60

Any ideas on how to set up the lookup would be greatly appreciated.

Copy and paste the below formula.

=INDEX($E$2:$E$11,MATCH(1,($A$2:$A$11="CENTER2")*( $B$2:$B$11="CLIENT 4")))

After pasting the formula give F2 and press Cntrl+Shift+Enter since it is an
array formula.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"HowardM" wrote:

Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2

Thank you for your reply.

I also need it to match the appropriate month which is also a variable as
well. Not sure if that is clear....if the formula that is looking up the
value is in a column that is entitled "Jan", then I need it to find the value
for Center 2, Client 4 that is in the Jan Column. If the lookup formula is
in a column entitled "Mar" then it needs to lookup Center 2, Client 4 that is
in the Mar column. How does that change the formula?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula.

=INDEX($E$2:$E$11,MATCH(1,($A$2:$A$11="CENTER2")*( $B$2:$B$11="CLIENT 4")))

After pasting the formula give F2 and press Cntrl+Shift+Enter since it is an
array formula.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"HowardM" wrote:

Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2

Revised table

With a different table structure then you have a few options.

Lookup values:

G1 =Center2
H1 = Client 4
I1 = Mar

Option 1

Array entered**

=INDEX(C2:E11,MATCH(1,IF(A2:A11=G1,IF(B2:B11=H1,1) ),0),MATCH(I1,C1:E1,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Option 2:

=SUMPRODUCT(--(A2:A11=G1),--(B2:B11=H1),INDEX(C2:E11,,MATCH(I1,C1:E1,0)))

Option 3:

=SUMPRODUCT((A2:A11=G1)*(B2:B11=H1)*(C1:E1=I1)*C2: E11)

--
Biff
Microsoft Excel MVP


"HowardM" wrote in message
...
Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2

For example, if you put Center2 in A13, Client4 in B13 and Feb in C13 then
put the following formula in Cell A15:
=INDEX($A$1:$E$11,(MATCH(A13&B13,A2:A11&B2:B11,0)+ 1),MATCH(C13,A1:E1,0)).
Please entre as arraye formula. and you will get different lookup value if
you change any criteria in cell A13 B13 or C13.

"HowardM" wrote:

Thank you for your reply.

I also need it to match the appropriate month which is also a variable as
well. Not sure if that is clear....if the formula that is looking up the
value is in a column that is entitled "Jan", then I need it to find the value
for Center 2, Client 4 that is in the Jan Column. If the lookup formula is
in a column entitled "Mar" then it needs to lookup Center 2, Client 4 that is
in the Mar column. How does that change the formula?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula.

=INDEX($E$2:$E$11,MATCH(1,($A$2:$A$11="CENTER2")*( $B$2:$B$11="CLIENT 4")))

After pasting the formula give F2 and press Cntrl+Shift+Enter since it is an
array formula.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"HowardM" wrote:

Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2

Thank you so much. Works like a charm. Can you help me understand what the
+1 represents?

"Smoking" wrote:

For example, if you put Center2 in A13, Client4 in B13 and Feb in C13 then
put the following formula in Cell A15:
=INDEX($A$1:$E$11,(MATCH(A13&B13,A2:A11&B2:B11,0)+ 1),MATCH(C13,A1:E1,0)).
Please entre as arraye formula. and you will get different lookup value if
you change any criteria in cell A13 B13 or C13.

"HowardM" wrote:

Thank you for your reply.

I also need it to match the appropriate month which is also a variable as
well. Not sure if that is clear....if the formula that is looking up the
value is in a column that is entitled "Jan", then I need it to find the value
for Center 2, Client 4 that is in the Jan Column. If the lookup formula is
in a column entitled "Mar" then it needs to lookup Center 2, Client 4 that is
in the Mar column. How does that change the formula?

"Ms-Exl-Learner" wrote:

Copy and paste the below formula.

=INDEX($E$2:$E$11,MATCH(1,($A$2:$A$11="CENTER2")*( $B$2:$B$11="CLIENT 4")))

After pasting the formula give F2 and press Cntrl+Shift+Enter since it is an
array formula.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"HowardM" wrote:

Same question as to how to complete lookup but I have changed to table to
remove the issue of it being in a pivot table. Revised table below.

Any help would be appreciated.

A B C D E
1 Center Client Jan Feb Mar

2 Center1 Client 1 0 3 0
3 Center1 Client 2 131 108 107
4 Center1 Client 3 4 0 0
5 Center1 Client 4 25 24 24
6 Center1 Client 5 1 0 7
7 Center2 Client 1 0 0 0
8 Center2 Client 2 20 4 4
9 Center2 Client 3 0 1 6
10 Center2 Client 4 36 32 48
11 Center2 Client 5 11 1 2