Match, Index, If

[Shortstopper00]

I'm making a user based questionaire. I need them to input 4 different
variables and based on those variables I need a single number to be picked
out of an index. I'm not exactly sure how the code would look. Here is an
example of what I am trying to do. The city, sunny or cloudy, month and time
(1,2,3) are the variables.

1 2 3
Toronto Sunny Jan 4.5 5.7 6.7
Toronto Cloudy Jan 34 3 12
Ottawa Sunny Jan 23.5 45 14
Ottawa Cloudy Jan 32 4.8 54

 

[smartin]

I'm making a user based questionaire. I need them to input 4 different
variables and based on those variables I need a single number to be picked
out of an index. I'm not exactly sure how the code would look. Here is an
example of what I am trying to do. The city, sunny or cloudy, month and time
(1,2,3) are the variables.

1 2 3
Toronto Sunny Jan 4.5 5.7 6.7
Toronto Cloudy Jan 34 3 12
Ottawa Sunny Jan 23.5 45 14
Ottawa Cloudy Jan 32 4.8 54

One way...

I arranged your sample table in A1:F5 and the criteria (#; City; Sunny
etc; Month) in H1:H4. This is an array formula (commit with
Ctrl+Shift+Enter, not just Enter):

=INDEX(D2:F5,MATCH(1,(H2=A2:A5)*(H3=B2:B5)*(H4=C2:C5),0),MATCH(H1,D1:F1,0))

 

[T. Valko]

Try this...

A2:A5 = city
B2:B5 = status
D1:F1 = 1,2,3
D2:F5 = values

Input cells:

A10 = city
B10 = status
C10 = 1, 2 or 3

=SUMPRODUCT(--(A2:A5=A10),--(B2:B5=B10),INDEX(D2:F5,,C10))

 

[Max]

Assuming your source table as posted is in A1:F5
Assume the 4 var inputs are in H2:K2, eg: Toronto, Cloudy, Jan, 2
Put in L2, normal ENTER
=INDEX(D2:F5,MATCH(1,INDEX((A2:A5=H2)*(B2:B5=I2)*(C2:C5=J2),),0),MATCH(K2,D1:F1,0))
L2 will retrieve the required intersection value, ie: 3
The above expression is generic, it will work even if the intersection
values are text (unlike sumproduct versions)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik