Creating a look up function

  • Thread starter Thread starter Jason Mills
  • Start date Start date
J

Jason Mills

I am trying to create a lookup function that will return a
value based on 3 criteria. For example, depending on the
values in column A, B, and C I need it to calculate the
appropriate response that I have indicated in column D.

Here is my best guess at a formula:

=INDEX(D2:D6,MATCH(A1&B1&C1,A2:A6&B2:B6&C2:C6,0))

I have done this formula when determine a value based on
on two criteria, but not three. I am not sure if the
match function works for 3 variables.

Thanks for any help!!
 
Jason Mills wrote...
...
Here is my best guess at a formula:

=INDEX(D2:D6,MATCH(A1&B1&C1,A2:A6&B2:B6&C2:C6,0))
Click to expand...
...

This should work as long as you enter it as an array formula, but
better approach (also an array formula) would be

=INDEX(D2:D6,MATCH(1,(A2:A6=A1)*(B2:B6=B1)*(C2:C6=C1),0)
 
Back
Top