Return a third cell value at the location of another cell if two other cells match
https://i.stack.imgur.com/WJAS0.png (Sorry for the link, I don't have enough rep to post an image)
I'm trying to get a formula where if any of the values found in column D (taking into account that some cells have multiple values ie. cell D4) match a value in Column A then return the value of the same row associated with Column D.
For example, in cell D4 the number 786403213972 matches A2 then cell C4 will return 100.
1 answer

Following up on your last question here, hereby a way to do this (beware, it's long and tricky)
Formula in
C2
:=IF(SUMPRODUCT((ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(D2,CHAR(10),REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(D2)LEN(SUBSTITUTE(D2,CHAR(10),""))+1))1)*99+((ROW(OFFSET($A$1,,,LEN(D2)LEN(SUBSTITUTE(D2,CHAR(10),""))+1)))=1),99)),TRANSPOSE($A$2:$A$7)))))>0,B2,"")
Confirm as array formula through Ctrl+Shift+Enter
Drag down...