Lookup assistance needed

[HowardM]

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]

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 here:
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))

 

[Dave Peterson]

ps. I think option 1 is easier.

<<snipped>>

 

[HowardM]

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

 

[T. Valko]

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))

 

[Ms-Exl-Learner]

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!

 

[HowardM]

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?

 

[T. Valko]

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)

 

[Smoking]

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]

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