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

B</SPAN>	C</SPAN>
1-Feb-13</SPAN>	394,907.66</SPAN>
31-Jan-13</SPAN>	25,000,000.00</SPAN>
1-Feb-13</SPAN>	3,222,853</SPAN>
4-Feb-13</SPAN>	11,160,183.08</SPAN>
4-Feb-13</SPAN>	249,193,230.00</SPAN>
5-Feb-13</SPAN>	900,498.66</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Sheet 2

K</SPAN>	L</SPAN>
394709.5</SPAN>	1-Feb-13</SPAN>
3,222,800</SPAN></SPAN>	1-Feb-13</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

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

B</SPAN>	C</SPAN>
1-Feb-13</SPAN>	394,907.66</SPAN>
31-Jan-13</SPAN>	25,000,000.00</SPAN>
1-Feb-13</SPAN>	3,222,853</SPAN>
4-Feb-13</SPAN>	11,160,183.08</SPAN>
4-Feb-13</SPAN>	249,193,230.00</SPAN>
5-Feb-13</SPAN>	900,498.66</SPAN>

<TBODY>
</TBODY>

Sheet 2

K</SPAN>	L</SPAN>
394709.5</SPAN>	1-Feb-13</SPAN>
3,222,800</SPAN></SPAN>	1-Feb-13</SPAN>

<TBODY>
</TBODY>

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