VLookup or index/match help with date ranges in criteria 2

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
OK, I had asked a similar question earlier in the link below:

http://www.mrexcel.com/board2/viewtopic.php?t=235651

However, the requirements have slightly changed and I now have an additional criteria that I need to include / match-up.

Please see the posted spreadsheet<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
Book2
ABCDEFGH
1Sales
2Sales NoProductSupplierPurchase DateSales DateSales valuePurchase valueMargin
31Baked BeansABC15/08/200616/08/20060
42Baked BeansABC15/08/200603/09/20060
53Salt BeefXYZ02/09/200615/09/20060
6
7
8Sales Values
9ProductSupplierDate fromDate toValue
10Baked BeansABC01/08/200631/08/200612
11Baked BeansABC01/09/200630/09/200617
12Salt BeefXYZ01/09/200630/09/200622
13
14
15Purchase Values
16ProductSupplierDate fromDate toValue
17Baked BeansABC01/08/200631/08/200610
18MacaroniABC01/07/200631/07/20062
19Salt BeefXYZ01/09/200630/09/200611
Sheet1
</SPAN><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003</FONT><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 15th May 2003</FONT></HR></BODY></HTML>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
mrexcel.com.xls
ABCDEFGH
1Sales
2SalesNoProductSupplierPurchaseDateSalesDateSalesvaluePurchasevalueMargin
31BakedBeansABC15/08/200616/08/20060
42BakedBeansABC15/08/200603/09/20060
53SaltBeefXYZ02/09/200615/09/20060
6
7
8SalesValues
9ProductSupplierDatefromDatetoValue
10BakedBeansABC01/08/200631/08/200612
11BakedBeansABC01/09/200630/09/200617
12SaltBeefXYZ01/09/200630/09/200622
13
14
15PurchaseValues
16ProductSupplierDatefromDatetoValue
17BakedBeansABC01/08/200631/08/200610
18MacaroniABC01/07/200631/07/20062
19SaltBeefXYZ01/09/200630/09/200611
Sheet1
</SPAN><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003</FONT><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 15th May 2003</FONT></HR></BODY></HTML>
 

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13
<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
mrexcel.com.xls
ABCDEFGH
1Sales
2SalesNoProductSupplierPurchaseDateSalesDateSalesvaluePurchasevalueMargin
31BakedBeansABC15/08/200616/08/20060
42BakedBeansABC15/08/200603/09/20060
53SaltBeefXYZ02/09/200615/09/20060
6
7
8SalesValues
9ProductSupplierDatefromDatetoValue
10BakedBeansABC01/08/200631/08/200612
11BakedBeansABC01/09/200630/09/200617
12SaltBeefXYZ01/09/200630/09/200622
13
14
15PurchaseValues
16ProductSupplierDatefromDatetoValue
17BakedBeansABC01/08/200631/08/200610
18MacaroniABC01/07/200631/07/20062
19SaltBeefXYZ01/09/200630/09/200611
Sheet1
</SPAN><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003</FONT><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 15th May 2003</FONT></HR></BODY></HTML>
 

abdul hafeel

New Member
Joined
Oct 14, 2006
Messages
9
Hi,

I have created a Multi Column Vlookup for Excel. It has some bugs. I have posted the code in the forum. Have a look at it let me know if its useful
 

saboorsas

New Member
Joined
Aug 19, 2003
Messages
13

ADVERTISEMENT

mrexcel.com.xls
ABCDEFGH
1Sales
2Sales NoProductSupplierPurchase DateSales DateSales valuePurchase valueMargin
31Baked BeansABC15/08/200616/08/20060
42Baked BeansABC15/08/200603/09/20060
53Salt BeefXYZ02/09/200615/09/20060
6
7
8Sales Values
9ProductSupplierDate fromDate toValue
10Baked BeansABC01/08/200631/08/200612
11Baked BeansABC01/09/200630/09/200617
12Salt BeefXYZ01/09/200630/09/200622
13
14
15Purchase Values
16ProductSupplierDate fromDate toValue
17Baked BeansABC01/08/200631/08/200610
18MacaroniABC01/07/200631/07/20062
19Salt BeefXYZ01/09/200630/09/200611
Sheet1
 

Forum statistics

Threads
1,136,878
Messages
5,678,307
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top