Probably So Easy It's Embarassing!

[MurcksPoms]

Hello! I feel like a dummy asking this as it is probably the most simple thing ever but I have a major headache trying to work it out!! :banghead:
Here is what I am trying to tell Excel to do...

If the # in worksheet CHECKBOOK!N:N is a "2"
And the Text in CHECKBOOK!C:C matches "TMOBILE"
Paste whatever number is in CHECKBOOK!E:E
Into Worksheet Cell BILLS!B25

I have no idea how I keep getting this screwed up...
Any help is so greatly appreciated!!!
Jennifer

[mdmackillop]

=INDEX(CheckBook!E1:E1000,SUMPRODUCT(--(CheckBook!C1:C1000="TMOBILE"),--(CheckBook!N1:N1000=2),ROW(A1:A1000)))

[duluter]

In your example, you use N:N, C:C, etc. And you want the answer to go into cell B25. I don't understand how you can get "one answer" using this notation. How will the formula know which row you are interested in?


Duluter

[Immatoity]

why not create another column (eg column o)and combine "tmobile" and "2" and use that column to be "looked up" against

step 1 = formula in column 0 =CONCATENATE(C3&N3) etc
step 2 = in cell b26 (or any "spare" cell in sheet "bills") simply type the text TMOBILE2
step3 = in cell B25 in bills use a sumif ie =SUMIF(Checkbook!O:O,bills!B26,Checkbook!E:E)

I tried it and it works...

[mdmackillop]

Thanks Immatoity,, I missed that column E was a number. That simplifies SumProduct.


=SUMPRODUCT(--(Checkbook!C1:C10000="TMOBILE"),--(Checkbook!N1:N10000=2),(Checkbook!E1:E10000))

Jennifer, these answers depend on unique solutions in your data. There is no check for that.

[Immatoity]

Thanks Immatoity,, I missed that column E was a number. That simplifies SumProduct.


=SUMPRODUCT(--(Checkbook!C1:C10000="TMOBILE"),--(Checkbook!N1:N10000=2),(Checkbook!E1:E10000))

Jennifer, these answers depend on unique solutions in your data. There is no check for that.

not used sumproduct before... cheers mdmackillop..will have a play later

[mdmackillop]

Read all about it here (www.xldynamic.com/source/xld.SUMPRODUCT.html).

[MurcksPoms]

Hello! I tried the above solutions and they all worked great!!
Thanks so much for the help everyone!!!

Jennifer