Multiple column v-look up

what if

	B	C	D	E
1	Description	Designation	Summary	Result
2	work in the G-insurance department	Analyst	analysing data
3	operating in G-insurance department	Strategic Head, life division	heads the life division of the company	???
4	In the retirement deprtment, all the retirement issues are handled	Strategic planning in retirement division	heading L&R department
5	in the operations, daily routine works are handled	Operations head	daily routine works

In such cases, it should give another flag such as "conflict" so that I can manually filter for conflicting cases and decide their final category

Originally Posted by sandy666

what if

	B	C	D	E
1	Description	Designation	Summary	Result
2	work in the G-insurance department	Analyst	analysing data
3	operating in G-insurance department	Strategic Head, life division	heads the life division of the company	???
4	In the retirement deprtment, all the retirement issues are handled	Strategic planning in retirement division	heading L&R department
5	in the operations, daily routine works are handled	Operations head	daily routine works

Maybe put this on E2 and copied down


=IF(OR(B2="",C2="",D2=""),"",IFERROR(IFERROR(IFERROR(IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($I$2:$I$9," ","*"),B2),$J$2:$J$9),LOOKUP(1000,SEARCH(LEFT($I$2:$I$9,FIND(" ",$I$2:$I$9&" ")-1),B2),$J$2:$J$9)),IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($I$2:$I$9," ","*"),C2),$J$2:$J$9),LOOKUP(1000,SEARCH(LEFT($I$2:$I$9,FIND(" ",$I$2:$I$9&" ")-1),C2),$J$2:$J$9))),IFERROR(LOOKUP(1000,SEARCH(SUBSTITUTE($I$2:$I$9," ","*"),D2),$J$2:$J$9),LOOKUP(1000,SEARCH(LEFT($I$2:$I$9,FIND(" ",$I$2:$I$9&" ")-1),D2),$J$2:$J$9))),"None"))