PDA

View Full Version : Solved: index/match with more than one match



Melinda
05-25-2009, 06:22 PM
I know that match function will find the first value that is equal to the criteria. So I have a worksheet that contains an index/match function that needs to pull all the values that is equal to the criteria.

Once I match cell B26 in the L36 worksheet to the values in Column C of the TABLE worksheet, I retreive the values in Column B of the TABLE worksheet. I would like to sum all of the items in Column N of the SAP worksheet that matches all of the values in Column B.

The formula I am using is SUMPRODUCT(--(INDEX(Table!$B:$B,MATCH(B26,Table!$C:$C,0))=SAP!$N:$N),--(LEFT($C$26,1)=LEFT(SAP!$I:$I,1)),--(MID(SAP!$K:$K,2,2)&RIGHT(SAP!$K:$K,2)="0"&$D30),--($B$2>=SAP!$P:$P),(SAP!$O:$O)). The answer should be $3,500.00 but I keep coming back with $0 because it is pulling 0100.000000.000 and not L360A00010_0000.

Can someone help me, please?

Melinda

anandbohra
05-25-2009, 11:50 PM
pl upload xls file so that all users can look into the problem properly (xlsx working only in Excel-2007)

Melinda
05-26-2009, 04:42 AM
anandbohra,

I have upload the file as a xls file. Thanks for letting me know that other people could not view my file.

Melinda

anandbohra
05-27-2009, 02:24 AM
Once I match cell B26 in the L36 worksheet to the values in Column C of the TABLE worksheet, I retreive the values in Column B of the TABLE worksheet. I would like to sum all of the items in Column N of the SAP worksheet that matches all of the values in Column B.

as far as I tried my bast it is not possible through normal excel function as the return value can be 1 or many (looking in sheet named table col c)
so I designed a custom function (UDF)
which first check all instances & one by one use sumif to get result

Function Manylookup_sum(lookup_Value As Variant, lookup_range As Range, column_no As Integer, findrange As Range, sumrange As Range) As Variant
Dim xVal As Variant
Dim myColl As New Collection

On Error Resume Next
For Each xVal In lookup_range
If CStr(xVal.Value) = CStr(lookup_Value.Value) Then
myColl.Add Item:=xVal.Offset(0, column_no - 1)
End If
Next xVal
On Error GoTo 0

For Each xVal In myColl
Manylookup_sum = Manylookup_sum + Application.WorksheetFunction.SumIf(findrange, xVal, sumrange)
Next xVal
End Function




also refer attached sheet & check the output in cell E26 in sheet L36 is right as per your calculation so that we can move forward.
if found wrong then provide exact answer & logic for the same

Bob Phillips
05-27-2009, 03:24 AM
pl upload xls file so that all users can look into the problem properly (xlsx working only in Excel-2007)

Don't you have the 2007 compatibility packs?

anandbohra
05-27-2009, 03:32 AM
Don't you have the 2007 compatibility packs?
No I don't have.

just googled it & downloading from Microsoft site

Thanks for the information regarding compatibility pack

Melinda
05-27-2009, 06:30 AM
anandbohra,

That custom function is still pulling only 0100.000000.000 and not L360A00010_0000.

Melinda

anandbohra
05-27-2009, 09:43 PM
As like Vlookup my UDF is also searching from left to right so u have to do a slight modification to your data in sheet named table
swap column B with column C
so it will match B26 of sheet L36 in column B & all the corresponding values in column C will be taken into consideration

hope u r clear now & as per that the sum of column O in sheet named SAP will yield result as 570468 (566968+3500)
566968 for 0100.000000.000
3500 for L360A00010_0000.

refer the attached workbook