search for a value in another column adn display the missing value in another column

[priyanka3006]

Hi
i have this sheet with me having data as shown below
A B c D E F
002 SI 002 SI
002 PW 004 SI
004 SI 01 DZ
004 PW 01 BI
01 DZ
01 BI
01 RF


what i want is compare A1 and B1 column with full column of C and D repectively
and put the missing collumn in E and F respectively.
like the output in E and F i am expecting is shown below:

E F
002 PW
004 PW
01 RF
coould you please help how to do that..

 

[shift-del]

Hello

Try this:
<table valign="middle" colspan="7" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="7" align="middle">Worksheet 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td><td align="middle">F</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">002</td><td align="left">SI</td><td align="left">002</td><td align="left">SI</td><td align="left">002</td><td align="left">PW</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="left">002</td><td align="left">PW</td><td align="left">004</td><td align="left">SI</td><td align="left">004</td><td align="left">PW</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="left">004</td><td align="left">SI</td><td align="left">01</td><td align="left">DZ</td><td align="left">01</td><td align="left">RF</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="left">004</td><td align="left">PW</td><td align="left">01</td><td align="left">BI</td><td align="left">
</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="left">01</td><td align="left">DZ</td><td align="right">
</td><td align="right">
</td><td align="left">
</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="left">01</td><td align="left">BI</td><td align="right">
</td><td align="right">
</td><td align="left">
</td><td align="left">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">7</td><td align="left">01</td><td align="left">RF</td><td align="right">
</td><td align="right">
</td><td align="left">
</td><td align="left">
</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: fixed; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>E1</td><td>{=IF(SUM(--(ISNA(MATCH($A$1:$A$7&$B$1:$B$7,$C$1:$C$4&$D$1:$D$4,0))))<ROWS(E$1:E1),"",INDEX(A$1:A$7,SMALL(IF(ISNA(MATCH($A$1:$A$7&$B$1:$B$7,$C$1:$C$4&$D$1:$D$4,0)),ROW($1:$7)),ROWS($1:1))))}</td></tr><tr><td>F1</td><td>{=IF(SUM(--(ISNA(MATCH($A$1:$A$7&$B$1:$B$7,$C$1:$C$4&$D$1:$D$4,0))))<ROWS(F$1:F1),"",INDEX(B$1:B$7,SMALL(IF(ISNA(MATCH($A$1:$A$7&$B$1:$B$7,$C$1:$C$4&$D$1:$D$4,0)),ROW($1:$7)),ROWS($1:1))))}</td></tr></tbody></table><table style="font-family: Arial; font-size: 8pt; background-color: rgb(255, 255, 255);"><tbody><tr><td style="font-weight: bold;">Array formula!</td></tr><tr><td>Do not enter the curly brackets {}.</td></tr><tr><td>Enter the formular with CTRL-SHIFT-ENTER instead of just ENTER.
</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created and displayed with Tab2HTML (v2.4.1). ©Gerd alias Bamberg
</td></tr></tbody></table>