If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 news 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 |
#7
|
|||
|
|||
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 news 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 |
#8
|
|||
|
|||
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 news 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 |
Thread Tools | |
Display Modes | |
|
|