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

 

[srizki]

I am sorry Domenic,

I made it little too complicated,
What it is that, I send a file to different users to input their comments in column M, comments like, "Completed", "In Process", "estimated complete date 08/2008", etc. When they send me back with comments, I want to put those comments on master file, to do that I match Account number in Column B and Project ID in column D in both the files user input file and the master file. So the formula should match these two values, value are text, and bring comments from column M of user file to master file. (Obviously formuls is on master file in column M).

I hope I presented it better this time.

Thanks

 

[Domenic]

Can you provide a small sample of the data, along with an example?

 

[srizki]

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[srizki]

USER FILE<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Domenic]

Try...

MF!E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX(User!$E$2:$E$11,MATCH(1,IF(RIGHT(User!$A$2:$A$11,4)=RIGHT(MF!A2,4),IF(User!$B$2:$B$11=MF!B2,1)),0))

To trap errors that may occur when no match occurs, try...

MF!E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(User!$E$2:$E$11,MATCH(1,IF(RIGHT(User!$A$2:$A$11,4)=RIGHT(MF!A2,4),IF(User!$B$2:$B$11=MF!B2,1)),0))))

Hope this helps!

 

[srizki]

Thanks Domenic,
I have not tried it yet, but why RIGHT function, when column A has same account numbers in both of the sheets. also in full sheet there are some account numbers that have right (cost centers) same and first five numbers are different, ie, cost center could be in more than one accout number, eg., 186000-1613, or 18700-1613, (6 and 7 in account numbers are years, 2006, 2007).

I will try these formulas

Thanks again Domenic.

 

[srizki]

Domenic, I used your Index formula with some adjustments, I used,
{=INDEX(USER E2:E11,MATCH(MF A2&B2,USER A2:A11&B2:B11,0))}

This worked with #N/A error where there are no match found, can you please refine this formula, so that I will not get the error.

I want to use SUMPRODUCT.
your help is appreciated.

Thanks
sohail

 

[Domenic]

I have not tried it yet, but why RIGHT function, when column A has same account numbers in both of the sheets.

If you're looking for an exact match on the whole account number, the RIGHT function is not needed.

{=INDEX(USER E2:E11,MATCH(MF A2&B2,USER A2:A11&B2:B11,0))}

This worked with #N/A error where there are no match found, can you please refine this formula, so that I will not get the error.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(USER!E2:E11,MATCH(MF!A2&B2,USER!A2:A11&B2:B11,0))))

...confirmed with CONTROL+SHIFT+ENTER.

I want to use SUMPRODUCT.

SUMPRODUCT cannot be used to return text...

 

[srizki]

Thank you very much Domenic.

 

[srizki]

It is working excellent, this what I wanted, though I did not understand the for part completely.