Vlookup between 2 sheets to look for value exact or within a range

[vinni.prasad]

Hi Team,
I have a work book with 2 sheets and am trying to compare 2 columns in sheet 1 with 2 colums in sheet 2.
In both the sheet one column among the 2 is off date and other one is some numbers.

I am looking for formula or macro which would compare the cols and highlight if date is common in sheet 1 and 2 and if so

then it should compare the corresponding number from sheet 2 to sheet.
I am aware the above can be done using vlookup but in my case the numbers in sheet 2 are not exactly same
it would be withing a range of +300 to -300. Below is the example

Sheet 1


[TABLE="width: 221"]
<TBODY>[TR]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[/TR]
[TR]
[TD]1-Feb-13</SPAN>[/TD]
[TD]394,907.66</SPAN>[/TD]
[/TR]
[TR]
[TD]31-Jan-13</SPAN>[/TD]
[TD]25,000,000.00</SPAN>[/TD]
[/TR]
[TR]
[TD]1-Feb-13</SPAN>[/TD]
[TD]3,222,853</SPAN>[/TD]
[/TR]
[TR]
[TD]4-Feb-13</SPAN>[/TD]
[TD]11,160,183.08</SPAN>[/TD]
[/TR]
[TR]
[TD]4-Feb-13</SPAN>[/TD]
[TD]249,193,230.00</SPAN>[/TD]
[/TR]
[TR]
[TD]5-Feb-13</SPAN>[/TD]
[TD]900,498.66</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

Sheet 2
[TABLE="width: 161"]
<TBODY>[TR]
[TD] K</SPAN>[/TD]
[TD] L</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]394709.5</SPAN>[/TD]
[TD="align: right"]1-Feb-13</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3,222,800</SPAN></SPAN>[/TD]
[TD="align: right"]1-Feb-13</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


In the above example when we compare 1-Feb-13 is common in both the sheet and data when compared it not exact but in the range of (+300 to -300) so these should get highlighted, not to forget even exact value should get highlighted.
Hope the above is clear.

Thansk and Regards,
Vinni Prasad

 

[Robert Mika]

Hi Team,
I have a work book with 2 sheets and am trying to compare 2 columns in sheet 1 with 2 colums in sheet 2.
In both the sheet one column among the 2 is off date and other one is some numbers.

I am looking for formula or macro which would compare the cols and highlight if date is common in sheet 1 and 2 and if so

then it should compare the corresponding number from sheet 2 to sheet.
I am aware the above can be done using vlookup but in my case the numbers in sheet 2 are not exactly same
it would be withing a range of +300 to -300. Below is the example

Sheet 1


[TABLE="width: 221"]
<TBODY>[TR]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[/TR]
[TR]
[TD]1-Feb-13</SPAN>
[/TD]
[TD]394,907.66</SPAN>
[/TD]
[/TR]
[TR]
[TD]31-Jan-13</SPAN>
[/TD]
[TD]25,000,000.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]1-Feb-13</SPAN>
[/TD]
[TD]3,222,853</SPAN>
[/TD]
[/TR]
[TR]
[TD]4-Feb-13</SPAN>
[/TD]
[TD]11,160,183.08</SPAN>
[/TD]
[/TR]
[TR]
[TD]4-Feb-13</SPAN>
[/TD]
[TD]249,193,230.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]5-Feb-13</SPAN>
[/TD]
[TD]900,498.66</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Sheet 2
[TABLE="width: 161"]
<TBODY>[TR]
[TD]K</SPAN>
[/TD]
[TD]L</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]394709.5</SPAN>
[/TD]
[TD="align: right"]1-Feb-13</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]3,222,800</SPAN></SPAN>
[/TD]
[TD="align: right"]1-Feb-13</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


In the above example when we compare 1-Feb-13 is common in both the sheet and data when compared it not exact but in the range of (+300 to -300) so these should get highlighted, not to forget even exact value should get highlighted.
Hope the above is clear.

Thansk and Regards,
Vinni Prasad

M2 down confirm control+Shift+Enter
=COUNT(IF(Sheet1!$B$2:$B$7=L3,IF(Sheet1!$C$2:$C$7>=K3-300,IF(Sheet1!$C$2:$C$7<=K3+300,1))))
You will get one if there is match or zero if is not