Compare a column (Dates) of a table to a row (Dates) in another table

[mrsimple]

I am trying to write a macro to compare data from two different tables and write a value in a different cell.


For Example


Would like to compare A1:A900 (Dates) of Table 1 to C1:AB1 (Dates) of Table 2 if true, then compare B1:B900 (Sites) of Table 1 to A1:A900 (Sites) of Table 2. If both conditions are met then I should be able to write the value found in say "c15"in a new cell.


Pls help.. I know this is confusing, if I can attach a sample sheet I would be able to but I am not sure how to do that.


THanks in advance, any help would be appreciated.

 

[oldchippy]

Hi,


See this sticky post


http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[mrsimple]

Thank you Old Chippy, ur a life saver...


Please find the link for the sample workbook I have tried to explain what I am exactly trying to achieve.


http://dl.dropbox.com/u/13693408/Sample%20Workbook.xlsx

 

[NARAYANK991]

Hi ,


Enter the following formula in cell F5 , as an array formula ( using CTRL SHIFT ENTER ) :


=OFFSET($K$5:$K$9,MATCH(D5&E5,$I$5:$I$9&$J$5:$J$9,0)-1,MATCH(C5,TRANSPOSE($K$4:$O$4),0)-1,ROWS($I$5:$I$9),1)


Copy it downwards.


Narayan

 

[Faseeh]

Hi mrsimple,


Following will also work:


=INDIRECT(ADDRESS(MATCH(D5,$I$5:$I$9,0)+ROW($J$4),MATCH(C5,$K$4:$O$4,0)+COLUMN($J$4)))


OR


=OFFSET($J$4,MATCH(C5,$K$4:$O$4,0),MATCH(D5,$I$5:$I$9))


OR a vlookup will also work


=VLOOKUP(E12,$J$5:$O$9,MATCH(C12,$K$4:$O$4,0)+1)


Enter in F5 and copy downwards.


Regards,

Faseeh

 

[Faseeh]

@ mrsimple,


I found the second formula in my last post some what erroneous, can't edit that post now so please ignore it.


Faseeh