# Lookup using multiple variables

#### sbrien

##### New Member
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
ABCDEFG
2StoreDay9-10 am10-11 am11-12 am12-1 pm1-2 pm
31Monday20.61%21.50%23.00%19.24%23.32%
42Monday20.47%19.75%23.83%25.01%24.82%
53Monday25.80%20.49%26.70%21.97%17.92%
61Tuesday17.76%37.16%23.37%21.99%20.55%
72Tuesday16.06%20.22%18.98%23.85%16.14%
83Tuesday18.55%22.19%16.80%19.16%23.21%
91Wednesday24.67%24.33%16.25%15.50%21.82%
102Wednesday23.53%43.14%3.92%19.61%7.84%
113Wednesday17.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
KLMNOP
19Store #
209-10 am10-11 am11-12 am12-1 pm1-2 pm
21Monday
22Tuesday
23Wednesday
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
=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.

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.

Here is another easy way:

Excel 2007
ABCDEFGH
4StoreDayInsert Column9-10 a.m.10-11.a.m11-12 a.m.12-01.p.m1-2 p.m.
51Monday1Monday437310214434785
62Monday2Monday836752193482408
73Monday3Monday672950279981509
81Tuesday1Tuesday17935018439239
92Tuesday2Tuesday8855127635185
103Tuesday3Tuesday212188863568399
111Wednesday1Wednesday802204896900289
122Wednesday2Wednesday45198323511593
133Wednesday3Wednesday46553500647797
14
15
16Store No.1
17
18
199-10 a.m.10-11.a.m11-12 a.m.12-01.p.m1-2 p.m.
20Monday437310214434785
21Tuesday17935018439239
22Wednesday802204896900289
Sheet1
Cell Formulas
RangeFormula
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.

This short video I did on explaining array formulas might help:

Hi Tom,

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?

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.

Replies
5
Views
686

1,203,545
Messages
6,056,033
Members
444,840
Latest member
RazzelDazel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back