Multiple criteria

[Solar Man]

Hi, I'm trying to find the formula for entering a value in a cell based on
multiple criteria.

I'm working on a solar heating sheet.
I have a drop down menu to select a State (only 2 at the moment) and another
drop down menu to select the angle of tilt on the solar panel. (8 angles to
choose from)

I would like it to work like this:
If the state is THIS, and the angle is THIS, then enter the value of THIS cell

I'm new but I have managed quite a complex worksheet so I can pick it up. I
am, however stumped at this point. Any help is appreciated. Thank you.

 

[T. Valko]

You need to build a table that lists the states and the angles. Like this:

...........A..........B..........C
1...................20.........25
2.......AL........2..........13
3.......TX........7..........10
4.......CA.......9...........17

Row 1 are the angles and column A are the states.

Then you can use a lookup function.

For example, assume the state drop down list is in cell X1 and the angle
drop down list is in cell X2.

X1 = TX
X2 = 25

=VLOOKUP(X1,A1:C4,MATCH(X2,A1:C1,0),0)

 

[Solar Man]

You need to build a table that lists the states and the angles. Like this:

...........A..........B..........C
1...................20.........25
2.......AL........2..........13
3.......TX........7..........10
4.......CA.......9...........17

Row 1 are the angles and column A are the states.

Then you can use a lookup function.

For example, assume the state drop down list is in cell X1 and the angle
drop down list is in cell X2.

X1 = TX
X2 = 25

=VLOOKUP(X1,A1:C4,MATCH(X2,A1:C1,0),0)


--
Biff
Microsoft Excel MVP





Thank you for your quick reply. I made a table and entered this formula

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E19,Sheet2!P37:Y37,0),Sheet2!L6)

The answer I got was 30 degrees. The answer I was looking for was the
contents of sheet 2 L6
I'm going to try some more but I wanted to thank you for replying

 

[T. Valko]

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E19,Sheet2!P37:Y37,0),Sheet2!L6)

What's in Sheet2!L6?

If you want an *exact* match of the angle that 4th argument must be either 0
or FALSE. If you want an "approximate" match then the 4th argument must be
either 1 or omitted *and* the angles must be listed in ascending order.

 

[Solar Man]

Sheet 2 L6 has the amount of solar radiation per solar panel in that state
and at that angle. The idea is to pick the angle and the state and that will
determine the amount of solar radiation I will get per panel. (L 6 ) At a
different angle the solar radiation will be different, or in a different
state.

I had to leave for the afternoon and I will get back to the problem tomorrow
afternoon. Again, thank you for your help.

 

[T. Valko]

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E19,Sheet2!P37:Y37,0),Sheet2!L6)

Ok, remove that reference from the formula: Sheet2!L6

It must resolve to either 1 (or TRUE) or 0 (or FALSE).

 

[Solar Man]

I've got it. What a relief. This is great and I can't thank you enough
for your help. Have a great day
Steve

 

[T. Valko]

You're welcome. Thanks for the feedback!