Creating Formula

[Bunderwood75]

My table

A B C D
1 Model Price Value Profit
2 SN1 153 763 333
3 SN2 876 788 987
4 SN3 769 331 768

If I enter the Model number SN3 in cell A5 and Value in cell A6, I need a formual that I can put in cell A7 that will return "331".

I'm sorry I didn't understand the previous attempts to help me.

Thanks!

 

[zacemmel]

{=sum(if((a5=a1:a4)*(a6=c1:c4), b1:b4,0))}

enter this as an array (control-shift-enter)
This message was edited by zacemmel on 2002-05-09 15:15

 

[Mark W.]

Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

=INDIRECT(A5) INDIRECT(A6)

... into cell A7.

 

[Aladin Akyurek]

On 2002-05-09 15:10, Bunderwood75 wrote:
My table

A B C D
1 Model Price Value Profit
2 SN1 153 763 333
3 SN2 876 788 987
4 SN3 769 331 768

If I enter the Model number SN3 in cell A5 and Value in cell A6, I need a formual that I can put in cell A7 that will return "331".

I'm sorry I didn't understand the previous attempts to help me.

Thanks!

I'm sorry I didn't understand the previous attempts to help me.

If you like, just ask a copy of the WB that shows how the OFFSET formula that I suggested works at:

aladin_akyurek@yahoo.com

Anycase, there is no need for using an array formula for this retrieval task.

 

[Yogi Anand]

On 2002-05-09 15:17, Mark W. wrote:
Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

=INDIRECT(A5) INDIRECT(A6)

... into cell A7.

Hi Bunderwood75:
If i may elaborate on what Mark W has provided ...
the space between the two INDIRECTs is the interesection operator,
so a caution here, after you enter the formula with the INTERSECT operator, Excel may offer to provide a needed correction by offering to substitute the space with the * character ... and of course that offer has to be rejected.

I hope I didn't go overboard on this one.

Regards!

 

[Yogi Anand]

On 2002-05-09 15:17, Mark W. wrote:
Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

=INDIRECT(A5) INDIRECT(A6)

... into cell A7.

Hi Mark:
Since you have facilitated Range Names as Labels already, I can also write my formula diectly without having to make entries in cells A5 and A6 (I do reconize that the OP had stated putting the values in cell A5 and A6)--

=SN3 Value

giving me a result of 331

Of course being able to write the formula with Label Names can be so much more meaningful as it provides for direct interpretation.

Regards!

 

[Mark W.]

On 2002-05-09 18:45, Yogi Anand wrote:

On 2002-05-09 15:17, Mark W. wrote:
Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

=INDIRECT(A5) INDIRECT(A6)

... into cell A7.

Hi Mark:
Since you have facilitated Range Names as Labels already, I can also write my formula diectly without having to make entries in cells A5 and A6 (I do reconize that the OP had stated putting the values in cell A5 and A6)--

=SN3 Value

giving me a result of 331

Of course being able to write the formula with Label Names can be so much more meaningful as it provides for direct interpretation.

Regards!

Yes, I know... but, as you stated that wouldn't satisfy the original problem statement.

 

[Yogi Anand]

Hi Mark: T H A N K S.

Regards!