Lookup using multiple variables

[sbrien]

Hello all,

I'm trying to do a lookup that references a table and uses three variables to locate the percent of traffic in a given store, on a given day, at a given time. I'm struggling with how to use multiple variables along the same axis (store # and day) because most of my experience with references is confined to VLOOKUP functions.

Excel 2010
	A	B	C	D	E	F	G
2	Store	Day	9-10 am	10-11 am	11-12 am	12-1 pm	1-2 pm
3	1	Monday	20.61%	21.50%	23.00%	19.24%	23.32%
4	2	Monday	20.47%	19.75%	23.83%	25.01%	24.82%
5	3	Monday	25.80%	20.49%	26.70%	21.97%	17.92%
6	1	Tuesday	17.76%	37.16%	23.37%	21.99%	20.55%
7	2	Tuesday	16.06%	20.22%	18.98%	23.85%	16.14%
8	3	Tuesday	18.55%	22.19%	16.80%	19.16%	23.21%
9	1	Wednesday	24.67%	24.33%	16.25%	15.50%	21.82%
10	2	Wednesday	23.53%	43.14%	3.92%	19.61%	7.84%
11	3	Wednesday	17.51%	25.97%	28.84%	19.22%	21.14%
Sheet1

I want to use those values to populate the following table when I enter a store number into the yellow cell:

Excel 2010
	K	L	M	N	O	P
19	Store #
20		9-10 am	10-11 am	11-12 am	12-1 pm	1-2 pm
21	Monday
22	Tuesday
23	Wednesday
Sheet6

I tried using a SUMIFS, but that gave me a #value error. I figured there is a way to use an array function but I haven't really ventured into learning those yet, though I'm willing if necessary...

I appreciate any suggestions.

 

[Tom Urtis]

See if the essence of this link and example helps you.

 

[kpark91]

=INDEX(Sheet1!$A$2:$G$13, MATCH($L$19,Sheet1!$A:$A,0) - 3 + MATCH($K21,Sheet1!$B:$B,0),MATCH(L$20,Sheet1!$2:$2,0))

Put this formula into Sheet6!L21 and copy down and right.
No need for array formula.

 

[sbrien]

See if the essence of this link and example helps you.

This worked perfectly. I guess I should learning a bit more about arrays so I can understand the formulas I'm using. I appreciate the help!

kpark91 - Thanks! I'll throw that in my model and see how it works.

 

[Mindpsyche]

Here is another easy way:

Excel 2007
	A	B	C	D	E	F	G	H
4	Store	Day	Insert Column	9-10 a.m.	10-11.a.m	11-12 a.m.	12-01.p.m	1-2 p.m.
5	1	Monday	1Monday	437	310	214	434	785
6	2	Monday	2Monday	836	752	193	482	408
7	3	Monday	3Monday	672	950	279	981	509
8	1	Tuesday	1Tuesday	179	350	184	392	39
9	2	Tuesday	2Tuesday	885	51	276	351	85
10	3	Tuesday	3Tuesday	212	188	863	568	399
11	1	Wednesday	1Wednesday	802	204	896	900	289
12	2	Wednesday	2Wednesday	45	198	323	511	593
13	3	Wednesday	3Wednesday	46	553	500	647	797
14
15
16	Store No.	1
17
18
19		9-10 a.m.	10-11.a.m	11-12 a.m.	12-01.p.m	1-2 p.m.
20	Monday	437	310	214	434	785
21	Tuesday	179	350	184	392	39
22	Wednesday	802	204	896	900	289
Sheet1

Cell Formulas
Range	Formula
C5	=A5&B5
C6	=A6&B6
C7	=A7&B7
C8	=A8&B8
C9	=A9&B9
C10	=A10&B10
C11	=A11&B11
C12	=A12&B12
C13	=A13&B13
C20	=INDEX($D$5:$H$13,MATCH($B$16&$A20,$C$5:$C$13,0),MATCH(C$19,$D$4:$H$4,0))
C21	=INDEX($D$5:$H$13,MATCH($B$16&$A21,$C$5:$C$13,0),MATCH(C$19,$D$4:$H$4,0))
C22	=INDEX($D$5:$H$13,MATCH($B$16&$A22,$C$5:$C$13,0),MATCH(C$19,$D$4:$H$4,0))
D5	=RANDBETWEEN(10,1000)
D6	=RANDBETWEEN(10,1000)
D7	=RANDBETWEEN(10,1000)
D8	=RANDBETWEEN(10,1000)
D9	=RANDBETWEEN(10,1000)
D10	=RANDBETWEEN(10,1000)
D11	=RANDBETWEEN(10,1000)
D12	=RANDBETWEEN(10,1000)
D13	=RANDBETWEEN(10,1000)
D20	=INDEX($D$5:$H$13,MATCH($B$16&$A20,$C$5:$C$13,0),MATCH(D$19,$D$4:$H$4,0))
D21	=INDEX($D$5:$H$13,MATCH($B$16&$A21,$C$5:$C$13,0),MATCH(D$19,$D$4:$H$4,0))
D22	=INDEX($D$5:$H$13,MATCH($B$16&$A22,$C$5:$C$13,0),MATCH(D$19,$D$4:$H$4,0))
E5	=RANDBETWEEN(10,1000)
E6	=RANDBETWEEN(10,1000)
E7	=RANDBETWEEN(10,1000)
E8	=RANDBETWEEN(10,1000)
E9	=RANDBETWEEN(10,1000)
E10	=RANDBETWEEN(10,1000)
E11	=RANDBETWEEN(10,1000)
E12	=RANDBETWEEN(10,1000)
E13	=RANDBETWEEN(10,1000)
E20	=INDEX($D$5:$H$13,MATCH($B$16&$A20,$C$5:$C$13,0),MATCH(E$19,$D$4:$H$4,0))
E21	=INDEX($D$5:$H$13,MATCH($B$16&$A21,$C$5:$C$13,0),MATCH(E$19,$D$4:$H$4,0))
E22	=INDEX($D$5:$H$13,MATCH($B$16&$A22,$C$5:$C$13,0),MATCH(E$19,$D$4:$H$4,0))
F5	=RANDBETWEEN(10,1000)
F6	=RANDBETWEEN(10,1000)
F7	=RANDBETWEEN(10,1000)
F8	=RANDBETWEEN(10,1000)
F9	=RANDBETWEEN(10,1000)
F10	=RANDBETWEEN(10,1000)
F11	=RANDBETWEEN(10,1000)
F12	=RANDBETWEEN(10,1000)
F13	=RANDBETWEEN(10,1000)
F20	=INDEX($D$5:$H$13,MATCH($B$16&$A20,$C$5:$C$13,0),MATCH(F$19,$D$4:$H$4,0))
F21	=INDEX($D$5:$H$13,MATCH($B$16&$A21,$C$5:$C$13,0),MATCH(F$19,$D$4:$H$4,0))
F22	=INDEX($D$5:$H$13,MATCH($B$16&$A22,$C$5:$C$13,0),MATCH(F$19,$D$4:$H$4,0))
G5	=RANDBETWEEN(10,1000)
G6	=RANDBETWEEN(10,1000)
G7	=RANDBETWEEN(10,1000)
G8	=RANDBETWEEN(10,1000)
G9	=RANDBETWEEN(10,1000)
G10	=RANDBETWEEN(10,1000)
G11	=RANDBETWEEN(10,1000)
G12	=RANDBETWEEN(10,1000)
G13	=RANDBETWEEN(10,1000)
H5	=RANDBETWEEN(10,1000)
H6	=RANDBETWEEN(10,1000)
H7	=RANDBETWEEN(10,1000)
H8	=RANDBETWEEN(10,1000)
H9	=RANDBETWEEN(10,1000)
H10	=RANDBETWEEN(10,1000)
H11	=RANDBETWEEN(10,1000)
H12	=RANDBETWEEN(10,1000)
H13	=RANDBETWEEN(10,1000)
B20	=INDEX($D$5:$H$13,MATCH($B$16&$A20,$C$5:$C$13,0),MATCH(B$19,$D$4:$H$4,0))
B21	=INDEX($D$5:$H$13,MATCH($B$16&$A21,$C$5:$C$13,0),MATCH(B$19,$D$4:$H$4,0))
B22	=INDEX($D$5:$H$13,MATCH($B$16&$A22,$C$5:$C$13,0),MATCH(B$19,$D$4:$H$4,0))

When you have more than one criteria, concatenate all the criteria into one cell in a new column and use that as the lookup value.

 

[Tom Urtis]

This worked perfectly. I guess I should learning a bit more about arrays so I can understand the formulas I'm using. I appreciate the help!

This short video I did on explaining array formulas might help:
http://www.youtube.com/watch?v=Yr_xuLB5JHs

 

[Mindpsyche]

This short video I did on explaining array formulas might help:
http://www.youtube.com/watch?v=Yr_xuLB5JHs

Hi Tom,

Thanks for the links.

I don't mean to hijack the thread but in this link:

http://www.atlaspm.com/toms-tutoria...l-intersecting-lookup-with-multiple-criteria/

In this formula = =INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))

I have understood everything in this formula except the part in the first match function Match(1, .... -> i cannot understand the logic behind this?

Can you please explain?

 

[Tom Urtis]

Sure...here's the explanation of why a 1 shows up as MATCH(1 in that formula.

The first argument of the MATCH funtion is the lookup_value. So why is it, you may be wondering, that a "1" is being looked up when clearly the column headers, row headers, and numbers in the table do not equal 1.

First, keep in mind this is an array formula. In the example I posted on that link, the array formula is
=INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))

Aslo keep in mind you are matching items in separate sets of array elements. Does element #1 of set 1 equal element #1 of set 2? The array formula is determining that behind the scenes and returns (as you'll see) either a 0 (meaning FALSE, or No) or a 1 (meaning TRUE, or Yes).

Suppose you reproduce the worksheet based on that that link example. Suppose further you select the cell containing that formula, which is cell A27.

Now, if you click into the formula bar, carefully select this portion of the formula...
(A1:A19=A24)*(B1:B19=B24)
...and press the F9 key. Don't hit Enter, just F9.

You will see this in the formula bar that is selected:
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}
and this as the larger formula in the formula bar at that moment:
=INDEX(A1:N19,MATCH(1,{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0},0),MATCH(C24,$A$1:$N$1,0))

Notice there is one "1" in the array of returned calculated elements of
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0}
and it happens to be in position (also known as index) #8. That is, the eighth index position in that array is a 1.

Notice further that the table in the example starts in row 1 and the formula includes row 1 in the entire table range arguments, for example
(A1:A19=A24)*(B1:B19=B24)

So, eyeball the eighth row down in the table, which coincidentally is the same as row 8 (index is a relative reference to the table range, not necessarily to the row number) and you will see that the criteria values in cells A24 ("Postage") and B24 ("2010") are the same as the values in cells A8 and B8 respectively. So, a TRUE match was found and the array properly returned a "1" for that element. And because a 1 was the lookup value, representing a TRUE match for the 1 that was found in what turned out to be the eighth element position of the array calculation per the F9 action described above, you know row 8 is the row of interest.

Again, index is relative. For example, if the table had started in row 4, the eighth element would have been grid row 11.

If you actually go through this exercise, remember to hit the Esc key to exit the calc method of F9, rather than hitting enter to commit the hard entry to the formula in the formula bar.