Return a third cell value at the location of another cell if two other cells match

0
votes

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
excelexcel-formula

1 Answers

0
votes

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