Index and Match Function for Separated Comma

Admin 1
Viewed 68

I have been trying to make a formula which INDEX the value of Sheet1.Rage("B2") and Matches with Sheet2.Rage("A2") then paste the result in Sheet2.Rage("B2")

The Match ID's i have is with separated comma in Sheet2.Rage("A2") like this BFXI77,CTIB60,KWPX76

and Sheet1.Rage("A2") has ID like this BFXI77.

So i want to SUM the result thrice in Sheet2.Rage("B2").

I have attached a Sheet link which may help better your help will be appreciated.

https://docs.google.com/spreadsheets/d/1I9kiYaTnaIS-EZ3QpN-6Z063Y2CGsDZNeZvYO5Z0bRw/edit?usp=sharing

=INDEX(Sheet1!B2:B27,MATCH(A2,Sheet1!A2:A27,0),1)
match excel-formula excel
2 Answers

In Excel you can use FILTERXML function in array formula:

=SUM(INDEX(Sheet1!B:B,N(IF(1,MATCH(FILTERXML("<a><b>" & SUBSTITUTE(A2,",","</b><b>") & "</b></a>","//b"),Sheet1!A:A,0)))))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

user_121888610

You need to split the 3 separate MatchIds at comma, and then do the VLOOKUP separately on each one.

It's a bit tricky because if there are not enough commas, you get #VALUE!, so each formulas for the second and third lookups have to check using ISERROR.

Like this: On sheet 2

B2 formula: comma 1 pos:  =FIND(",",A2)
C2 formula: comma 2 pos : =FIND(",",A2,B2+1)
D2 formula: lookup 1:     =LEFT(A2,IF(ISERROR(B2),LEN(A2),B2-1))
E2 formula: lookup 2:     =MID(A2,B2+1,IF(ISERROR(C2),LEN(A2),C2-1)-B2)
F2 formula: lookup 3:     =RIGHT(A2,LEN(A2)-C2)
G2 formula: value 1:      =VLOOKUP(D2,ItemTable,2,0)
H2 formula: value 2:      =IF(ISERROR(B2),,VLOOKUP(E2,ItemTable,2,0))
I2 formula: value 3:      =IF(ISERROR(C2),,VLOOKUP(F2,ItemTable,2,0))
J2 formula: Total:        =G2+H2+I2
user_1586750
Related