lookup formula with 3 variables to calculate

[bh24524]

Hi, so I have a table of data that looks like the following:

Basically, we are concerned with 4 columns Here - U, V, W, and X. Column U is where they enter a number. I have drop-down menus in columns V and W. V is a drop-down for the Classification choices above(A thru E) and W is a choice between either Grocery or Perish. Column X is basically where I'm looking for a formula that calculates the number in Column U by a corresponding rate depending on what is selected in both drop-down menus. So for example, if column U has 100 in it and V has C selected from its choices, and W has Perish Selected, I would want X to have 100 X .0875. If I selected E in column V and Grocery in W, I'd want column X to have 100 X .1308.

Is there a way to do this?

 

[RoryA]

Yes - using INDEX and MATCH. Where exactly is that lookup table (cell addresses)?

 

[bh24524]

Yes - using INDEX and MATCH. Where exactly is that lookup table (cell addresses)?

okay that table spans AC2:AE6 (row 1 is just the headers)

 

[RoryA]

Then the formula would be something like:

=U2*INDEX($AD$2:$AE$6,MATCH(V2,$AC$2:$AC$6,0),MATCH(W2,$AD$1:$AE$1,0))

 

[bh24524]

Then the formula would be something like:

=U2*INDEX($AD$2:$AE$6,MATCH(V2,$AC$2:$AC$6,0),MATCH(W2,$AD$1:$AE$1,0))

Neat! Thanks so much Rory! I've used Index/Match many many times before but never beyond only two columns of data, so this is good to know it is possible. If you get some time, would you mind explaining the logic of this formulas as it compares to if we only had two columns of data like I normally have used? Or point me in the direction of an explanation, although I think explaining using this example would be most helpful for me if you can. Thanks again!

 

[RoryA]

Sure.
The first argument to INDEX is the two columns of numbers only - that's the table to return results from.
The second one is a MATCH function that finds the row position we want the result from;
The third argument is another MATCH function that finds the column position (relative to the table).
So if V2 contains "C", the first match formula is really:
=MATCH("C",$AC$2:$AC$6,0)
so it's looking for the letter C in AC2:AC6, which it finds in position 3
Then if W2 contains "Grocery", we have:
=MATCH("Grocery",$AD$1:$AE$1,0)
which returns 1 since "Grocery" is in AD1, the first of the two columns. Putting those back into INDEX we have:
=INDEX($AD$2:$AE$6,3,1)
which means return the value in the 3rd row and 1st column of the range AD2:AE6, which is AD4.

Does that make sense?

 

[bh24524]

Sure.
The first argument to INDEX is the two columns of numbers only - that's the table to return results from.
The second one is a MATCH function that finds the row position we want the result from;
The third argument is another MATCH function that finds the column position (relative to the table).
So if V2 contains "C", the first match formula is really:
=MATCH("C",$AC$2:$AC$6,0)
so it's looking for the letter C in AC2:AC6, which it finds in position 3
Then if W2 contains "Grocery", we have:
=MATCH("Grocery",$AD$1:$AE$1,0)
which returns 1 since "Grocery" is in AD1, the first of the two columns. Putting those back into INDEX we have:
=INDEX($AD$2:$AE$6,3,1)
which means return the value in the 3rd row and 1st column of the range AD2:AE6, which is AD4.

Does that make sense?

It does, and just out of curiosity, are the Match portions interchangeable? So like if we go from

=U2*INDEX($AD$2:$AE$6,MATCH(V2,$AC$2:$AC$6,0),MATCH(W2,$AD$1:$AE$1,0))
To
=U2*INDEX($AD$2:$AE$6,MATCH(W2,$AD$1:$AE$1,0),MATCH(V2,$AC$2:$AC$6,0))

Where matches for V and W were swapped? Does it matter what order those would appear in?

 

[RoryA]

Yes, it does matter. The syntax for INDEX is:
=INDEX(lookup range, row, column)
if your lookup range is only one row or column, then you can just pass two arguments and use either row or column as the second one, but for a 2D range it has to be row first then column.

 

[bh24524]

Yes, it does matter. The syntax for INDEX is:
=INDEX(lookup range, row, column)
if your lookup range is only one row or column, then you can just pass two arguments and use either row or column as the second one, but for a 2D range it has to be row first then column.

Okay I get it now. Thank you for that, that was very helpful!