hi to all,
i have data like this sheet4
data have
Sample AB966 AB1609 AB1610 AB1611 AB1612 AB1613 AB1614
1 AA -- AA CC AA -- --
2 AC -- AA CC AA -- --
3 CC -- AA CC AA -- --
4 -- -- TT CC AA -- --
5 ZZ -- AA CC AA -- --
and data in sheet2
AB966 AB1609 AB1610 AB1611 AB1612 AB1613 AB1614
A AA -- AA CC AA -- --
B CC -- TT CC AA -- --
I want to replace in this sample 1 status (B3 in sheet4) for AB966 to A if it match to AB966 (B2 cell value in sheet2), replace to B if it match to AB966 ( B3 cell value in sheet2), IF B3 contains any mixed letters like AC then I would like to give H.
I would like to give -- for sample status (B3 in sheet4) if sample 1 contains any one of this letters ZZ or -- (in B2 in sheet4).
Now I am using combination of IF and Hlookup formula to solve this problem and it is working perfectly for AB966 and not working for others like AB1609 etc.
I am using this formula this, =IF(B3=HLOOKUP($I$2,Sheet2!$B$1:$AX$3,2,FALSE),"A",IF(B3=HLOOKUP($I$2,Sheet2!$B$1:$AX$3,3,FALSE),"B", IF(B3="ZZ","--",IF(B3="--","--","H"))))
now i want to drag this formula from AB966 to AB1614.
Expected results
AB966 AB1609 SX1610 SX1611 SX1612 SX1613 SX1614
A
H
B
--
--
Can anyone help to solve this problem and i am attching sample worksheet and it will help understand better than my explanation.
any help would be highly appreciated
Thanks,
Genetist
Bookmarks