Multiple criteria
 

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.

Multiple criteria
 

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


"Solar Man" <Solar wrote in message
...
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.

Multiple criteria
 

"T. Valko" wrote:

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


"Solar Man" <Solar wrote in message
...
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.


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

Multiple criteria
 

=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.

--
Biff
Microsoft Excel MVP


"Solar Man" wrote in message
...


"T. Valko" wrote:

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


"Solar Man" <Solar wrote in message
...
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.


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

Multiple criteria
 

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" wrote:

=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.

--
Biff
Microsoft Excel MVP

Multiple criteria
 

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E1 9,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).

--
Biff
Microsoft Excel MVP


"Solar Man" wrote in message
...
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" wrote:

=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.

--
Biff
Microsoft Excel MVP

Multiple criteria
 

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" wrote:

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E1 9,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).

--
Biff
Microsoft Excel MVP


"Solar Man" wrote in message
...
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" wrote:

=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.

--
Biff
Microsoft Excel MVP

Multiple criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Solar Man" wrote in message
...
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" wrote:

=VLOOKUP(Sheet1!G4,Sheet2!P37:Y38,MATCH(Sheet1!E1 9,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).

--
Biff
Microsoft Excel MVP


"Solar Man" wrote in message
...
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" wrote:

=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.

--
Biff
Microsoft Excel MVP