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

saboorsas · Oct 15, 2006

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

A

B

C

D

E

F

G

H

1

Sales

2

Sales No

Product

Supplier

Purchase Date

Sales Date

Sales value

Purchase value

Margin

3

1

Baked Beans

ABC

15/08/2006

16/08/2006

0

4

2

Baked Beans

ABC

15/08/2006

03/09/2006

0

5

3

Salt Beef

XYZ

02/09/2006

15/09/2006

0

6

7

8

Sales Values

9

Product

Supplier

Date from

Date to

Value

10

Baked Beans

ABC

01/08/2006

31/08/2006

12

11

Baked Beans

ABC

01/09/2006

30/09/2006

17

12

Salt Beef

XYZ

01/09/2006

30/09/2006

22

13

14

15

Purchase Values

16

Product

Supplier

Date from

Date to

Value

17

Baked Beans

ABC

01/08/2006

31/08/2006

10

18

Macaroni

ABC

01/07/2006

31/07/2006

2

19

Salt Beef

XYZ

01/09/2006

30/09/2006

11

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 · Oct 15, 2006

<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

A

B

C

D

E

F

G

H

1

Sales

2

SalesNo

Product

Supplier

PurchaseDate

SalesDate

Salesvalue

Purchasevalue

Margin

3

1

BakedBeans

ABC

15/08/2006

16/08/2006

0

4

2

BakedBeans

ABC

15/08/2006

03/09/2006

0

5

3

SaltBeef

XYZ

02/09/2006

15/09/2006

0

6

7

8

SalesValues

9

Product

Supplier

Datefrom

Dateto

Value

10

BakedBeans

ABC

01/08/2006

31/08/2006

12

11

BakedBeans

ABC

01/09/2006

30/09/2006

17

12

SaltBeef

XYZ

01/09/2006

30/09/2006

22

13

14

15

PurchaseValues

16

Product

Supplier

Datefrom

Dateto

Value

17

BakedBeans

ABC

01/08/2006

31/08/2006

10

18

Macaroni

ABC

01/07/2006

31/07/2006

2

19

SaltBeef

XYZ

01/09/2006

30/09/2006

11

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 · Oct 15, 2006

<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

A

B

C

D

E

F

G

H

1

Sales

2

SalesNo

Product

Supplier

PurchaseDate

SalesDate

Salesvalue

Purchasevalue

Margin

3

1

BakedBeans

ABC

15/08/2006

16/08/2006

0

4

2

BakedBeans

ABC

15/08/2006

03/09/2006

0

5

3

SaltBeef

XYZ

02/09/2006

15/09/2006

0

6

7

8

SalesValues

9

Product

Supplier

Datefrom

Dateto

Value

10

BakedBeans

ABC

01/08/2006

31/08/2006

12

11

BakedBeans

ABC

01/09/2006

30/09/2006

17

12

SaltBeef

XYZ

01/09/2006

30/09/2006

22

13

14

15

PurchaseValues

16

Product

Supplier

Datefrom

Dateto

Value

17

BakedBeans

ABC

01/08/2006

31/08/2006

10

18

Macaroni

ABC

01/07/2006

31/07/2006

2

19

SaltBeef

XYZ

01/09/2006

30/09/2006

11

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 · Oct 15, 2006

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 · Oct 15, 2006

mrexcel.com.xls

A

B

C

D

E

F

G

H

1

Sales

2

Sales No

Product

Supplier

Purchase Date

Sales Date

Sales value

Purchase value

Margin

3

1

Baked Beans

ABC

15/08/2006

16/08/2006

0

4

2

Baked Beans

ABC

15/08/2006

03/09/2006

0

5

3

Salt Beef

XYZ

02/09/2006

15/09/2006

0

6

7

8

Sales Values

9

Product

Supplier

Date from

Date to

Value

10

Baked Beans

ABC

01/08/2006

31/08/2006

12

11

Baked Beans

ABC

01/09/2006

30/09/2006

17

12

Salt Beef

XYZ

01/09/2006

30/09/2006

22

13

14

15

Purchase Values

16

Product

Supplier

Date from

Date to

Value

17

Baked Beans

ABC

01/08/2006

31/08/2006

10

18

Macaroni

ABC

01/07/2006

31/07/2006

2

19

Salt Beef

XYZ

01/09/2006

30/09/2006

11

Sheet1

saboorsas · Oct 15, 2006

This is my sample spreadsheet, sorry about the multiple posts ......

abdul hafeel · Oct 15, 2006

Hi SAS

Try this

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

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

saboorsas

New Member

Excel Facts

saboorsas

New Member

saboorsas

New Member

abdul hafeel

New Member

saboorsas

New Member

saboorsas

New Member

abdul hafeel

New Member

Similar threads

Forum statistics

Share this page

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

saboorsas

New Member

Excel Facts

saboorsas

New Member

saboorsas

New Member

abdul hafeel

New Member

saboorsas

New Member

saboorsas

New Member

abdul hafeel

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock