Index match with non unique values

[juanitadeb]

0000378G	FCP1 or FCP4	€ 39.44	0​	€ -	0​	€ -
0000447G	0​	€ -	0​	€ -	0​	€ -
0000450G	0​	€ -	0​	€ -	0​	€ -
0000458G	2.01.03	€ 70.47	2.01.07.01.02	€ 375.75	2.01.07.01.02	€ 228.13

So this is example of my data.

Now i want the addition of the amount for each value (2.01.07.01.02) for each ID.

I tried with IFNA(INDEX(TRANSPOSE(Sheet2!4:4),MATCH("2.04.02",TRANSPOSE(Sheet2!4:4),0)+1,1),0) but it was only finding the first one.

Is there a way to do this please?

 

[Peter_SSs]

Welcome to the MrExcel board!

Your requirement is not clear to me. Can you explain again more clearly?
Also ensure that you give the expected result for the sample data.

BTW, how would "2.04.02" match anything in that sample data?

 

[juanitadeb]

Welcome to the MrExcel board!

Your requirement is not clear to me. Can you explain again more clearly?
Also ensure that you give the expected result for the sample data.

BTW, how would "2.04.02" match anything in that sample data?

So basically I need to search for the number where 2.04.02 & 2.01.07.01.02 are just examples of the data of 30 columns and 10000 rows.
I want my output to be the price which is the next column, but for their respective ID, so by row.
Now since the number is not unique in a row i have a problem of outputting the addition of the prices in just one cell.
By the command i used it outputted only the first match it found.

Sorry if i am not explaining my self correctly.

 

[Peter_SSs]

Sorry if i am not explaining my self correctly.

It isn't really clear to us (or at least me) who are not familiar with your data and requirements.

What about making up a small but representative set of sample dummy data and enter the expected results manually into the worksheet, post all of that with XL2BB and explain how you got the expected results in that mini sheet?

 

[Legacy 143009]

Dear @Peter_SSs

I think what he needs is to match multiple criterias in horizontal like 2.01.03 and 2.01.07.01.02 together and sum adjacent matching values and write the sum at the end of that row. He has 30 columns of data like this in a single row and the formula will be at the 31st column for example.

I can only explain the scenario because the rest is way over for me

The result should return column numbers, offset one to left and sum them. Something like SUM(OFFSET(INDEX(4:4,1,MATCH(1,(4:4)=",2.01.03")*MATCH(1,(4:4)=",2.01.07.01.02")),0,1))
But somehow MATCH function should return an array of columns in a 30-column range. I can't do that part


Actually it looks more like a sumif function.

 

[juanitadeb]

Dear @Peter_SSs

I think what he needs is to match multiple criterias in horizontal like 2.01.03 and 2.01.07.01.02 together and sum adjacent matching values and write the sum at the end of that row. He has 30 columns of data like this in a single row and the formula will be at the 31st column for example.

I can only explain the scenario because the rest is way over for me

Yes, exactly! Thank you.

 

[Peter_SSs]

Yes, exactly! Thank you.

Then can you provide the sample data and expected results as requested? There should be no need for 30 columns to demonstrate the principle.

 

[Legacy 143009]

Yes, exactly! Thank you.

AE4:

=SUMIF(A4:AD4,"2.01.03",OFFSET(A4:AD4,0,1))+SUMIF(A4:G4,"2.01.07.01.02",OFFSET(A4:G4,0,1))

You should append additional SUMIF for each condition.

 

[Legacy 143009]

A more compact alternative:

=SUM(SUMIF(A4:AD4,{"2.01.03","2.01.07.01.02"},OFFSET(A4:AD4,0,1)))

 

[juanitadeb]

Then can you provide the sample data and expected results as requested? There should be no need for 30 columns to demonstrate the principle.

ID

PRODUCT

PRICE

PRODUCT

PRICE

PRODUCT

PRICE

PRODUCT

PRICE

Total of 2.01.07.01.02

10732120

2.01.02

€ 2,914.26

2.01.07.01.02

€ 5,018.89

2.01.07.01.02

€ 4,025.85

2.01.07.01.02

€ 18,277.35

€27322.09

My problem is with the last column.
Is this version ok or should I uploaded somewhere else? I am sorry but i do not know how to use the website.
Thank you so much.