Sumproduct And Right Function

[srizki]

I trying to match two columns of one table to two column of another in another workbook. One of the columns that needs to be matched has number with other numbers and I need only to match the four numbers at far end, therefore I used this formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$B$7:$B$1188=D10)*('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$C$7:$C$1188=RIGHT(B10,4)*'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$N$7:$N$1188))<o></o>
<o> </o>
Explanation, the two sheets are “Capital Expenditure & Approval Report”, and “<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:PersonName w:st="on">Ken</st1:PersonName> Report”, my formula is in <st1:PersonName w:st="on">Ken</st1:PersonName> report and the report has number like 18600-1413 and I need to match only 1413. <o></o>
The formula above works perfectly if I do not have to use =RIGHT function.<o></o>
<o> </o>
How can I ammend the formula to work.<o></o>
<o> </o>
Thanks.<o></o>
Sohail

 

[Domenic]

Assuming that the numbers in C7:C1184 are true numerical values and not text values, try...

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$B$7:$B$1188=D10),--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$C$7:$C$1188=RIGHT(B10,4)+0),'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$N$7:$N$1188)

If the numbers in C7:C1184 are formatted text, remove +0 from the formula.

Hope this helps!

 

[srizki]

Thank you Domenic,

I understood now, that is what Sailepty said above, but somewhow I was doing it wrong way.

Thanks to both of you.

 

[srizki]

I like to ask one more thing, so far my required value was a number, what if I want a sentence, using the same formula, except the last criteria is column say P that resides sentences like, "completed" " under process' etc.
I do not have to use Right Function.

Thanks

 

[Domenic]

To search for a value, let's say E10, within a text string in Column P, try...

=SUMPRODUCT(--('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$B$7:$B$1188=D10),--ISNUMBER(SEARCH(E10,'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$P$7:$P$1188)),'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$N$7:$N$1188)

Hope this helps!

 

[srizki]

Thank you Domenic

 

[srizki]

I am sorry; I did not explain correctly, the last part of formula should bring a text. I send to others to give me the status of their projects, they reply by filling column M in this case, saying, “completed”, “estimated completion date September 2009”, etc. etc. I need to bring their comments on my master file based on the two matching. The last part of formula is not working; I need to change it to get the text from column M. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Domenic]

Still somewhat unclear... Is this what you mean?

=INDEX('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$M$7:$M$1188,MATCH(1,IF('[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$B$7:$B$1188=D10,IF(ISNUMBER(SEARCH(E10,'[Capital Expenditures & Approvals Report-0308.xls]Capital Approvals'!$P$7:$P$1188)),1)),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[srizki]

Thank you Domenic.
Yes this is what I mean, I was trying to use this index and match, but without 1 and 0 at the end.
very appreciated.

 

[Domenic]

You're very welcome! Hopefully I understood correctly...

 

[srizki]

Yes! you did Domenic. I was not using IF function either. I was trying to use Index and match(1,(.... CSE,
that did not work.

Thanks