Hi,
Maybe something like this, dragging down the formula in each column. Note that you need to use CTRL-SHIFT-ENTER not just ENTER for columns E and G.
Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Site[/TD]
[TD]Location[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]site[/TD]
[TD]location[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]a[/TD]
[TD]in[/TD]
[TD="align: right"]12/01/2011[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]in[/TD]
[TD="align: right"]12/01/2011[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]b[/TD]
[TD]in[/TD]
[TD="align: right"]12/02/2011[/TD]
[TD][/TD]
[TD]b[/TD]
[TD]in[/TD]
[TD="align: right"]12/03/2011[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]c[/TD]
[TD]out[/TD]
[TD="align: right"]12/03/2011[/TD]
[TD][/TD]
[TD]f[/TD]
[TD]in[/TD]
[TD="align: right"]12/08/2011[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]d[/TD]
[TD]out[/TD]
[TD="align: right"]12/04/2011[/TD]
[TD][/TD]
[TD]h[/TD]
[TD]in[/TD]
[TD="align: right"]00/01/1900[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]e[/TD]
[TD]out[/TD]
[TD="align: right"]12/05/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]f[/TD]
[TD]in[/TD]
[TD="align: right"]12/06/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]g[/TD]
[TD]out[/TD]
[TD="align: right"]12/07/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]h[/TD]
[TD]in[/TD]
[TD="align: right"]12/08/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]i[/TD]
[TD]out[/TD]
[TD="align: right"]12/09/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010
Cell | Formula |
---|
F2 | =IF(E2<>"","in","") |
---|
<tbody>
[TD="bgcolor: #FFFFFF"]
Worksheet Formulas
[TABLE="class: html-maker-worksheet"]
<thead>[TR]
</thead><tbody>
</tbody>
[/TD]
[/TR]
</tbody>[/TABLE]
Cell | Formula |
---|
E2 | =IFERROR(INDEX(A$2:A$10,SMALL(IF(B$2:B$10="in",ROW(A$2:A$10)-ROW(A$2)+1),ROW(A1))),"") |
---|
G2 | =IFERROR(INDEX(C2:C10,MATCH(E2&F2,A$2:A$10&B$2:B$10,0)),"") |
---|
<tbody>
[TD="bgcolor: #FFFFFF"]
Array Formulas
[TABLE="class: html-maker-worksheet"]
<thead>[TR]
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter these manually yourself[/TD]
[/TR]
</tbody>[/TABLE]