Hi All,
First I would like to wish you all a happy and prosperous new year 2015!!!
The challenge this time faced by me is due to multiple probabilities across few cells on a row, wherein I have to manually based on the priorities pick one of the data as a result.
I have tried showing the requirements and the desired results based on a prioritization table in the attached spreadsheet, please look into this and provide your as usual support.
Thanks,
Sanjju
H2=INDEX($L$2:$L$12,MATCH(MAX(INDEX(MMULT(IFERROR((SEARCH(IF($A2:$D2<>"",$A2:$D2),$K$2:$K$12)>0)*1,0),(ROW(INDIRECT("1:"&COLUMNS($A$2:$D$12)))>0)*1),0)),INDEX((MMULT(IFERROR((SEARCH(IF($A2:$D2<>"",$A2:$D2),$K$2:$K$12)>0)*1,0),(ROW(INDIRECT("1:"&COLUMNS($A$2:$D$12)))>0)*1)=(LEN($K$2:$K$12)-LEN(SUBSTITUTE($K$2:$K$12,",","")))+1)*MMULT(IFERROR((SEARCH(IF($A2:$D2<>"",$A2:$D2),$K$2:$K$12)>0)*1,0),(ROW(INDIRECT("1:"&COLUMNS($A$2:$D$12)))>0)*1),0),0))
Try this array formula in H2 and copy towards down
Confirm with Ctrl+Shift+Enter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank you Siva, this formula worked like a charm for me. (I incorrectly listed two same combos in the prioritization table that you understood as well being a real expert). I appreciate all your help...
Thanks for your feedback and adding reputation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks