Matching and lookup

[tiredofit]

I have two tables as per below.

Table1

[TABLE="width: 267"]
<tbody>[TR]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]02/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]03/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]04/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]01/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]02/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]03/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]04/01/2010[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Table2

[TABLE="width: 267"]
<tbody>[TR]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[TD]Field To Return[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]02/01/2010[/TD]
[TD] aa[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]03/01/2010[/TD]
[TD] bb[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]04/01/2010[/TD]
[TD] cc[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD] dd[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]06/01/2010[/TD]
[TD] ee[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]02/01/2010[/TD]
[TD] aa[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]03/01/2010[/TD]
[TD] bb[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]04/01/2010[/TD]
[TD] cc[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD] dd[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]06/01/2010[/TD]
[TD] ee[/TD]
[/TR]
</tbody>[/TABLE]



I want to search for Field1 and Field2 in Table1 and find its corresponding row in Table2, then if the date in Table1 (or that row) is before the corresponding row in Table2, return its Field To Return value to the column labelled Result in Table1.

I have tried this formula:

=IF((A3=$I$3:$I$12)*(B3=$J$3:$J$12)*(C3<$K$3:$K$12),$L$3:$L$12,"BLANK")

When I enter "normally" I get this:

aa
bb
cc
dd
ee
aa
bb
cc
dd
ee

but if I enter it as an array formula, I only get this:

aa
BLANK
BLANK
BLANK
BLANK
aa
BLANK
BLANK
BLANK
BLANK
BLANK

What I am trying to achieve is search for Field1 and Field2 from Table 1 and look at "the whole of Table2", as opposed to row by row.

Can this be achieved using formulae or VBA?

Thanks

 

[JackDanIce]

I'd use a helper column in both tables i.e.

- new column before field1 in both tables
- fill with formula down "= field1 & field2"

Then you can use a VLOOKUP, like:

=IF(VLOOKUP(A2,table1<table1>,3,0) < C2, VLOOKUP(A2<c2,vlookup(a2,<table1>,table1,4,0),"")

You'll need to adjust this formula to your sheet and ranges</c2,vlookup(a2,<table1></table1>