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

  • answered 2019-07-11 07:25 JvdV

    Following up on your last question here, hereby a way to do this (beware, it's long and tricky)

    enter image description here

    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...