Vlookup with date range

[JangLang]

I have three sheets. From "Invoice", I want to find the correct lookup sheet based on year, match the LookupCode, and pull the correct Price where PurchaseDate is between BeginDate and EndDate. This one has kicked my behind. Any expertise welcome!

Sheet Invoice
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]LookupCode[/TD]
[TD]PurchaseDate[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]20122230325RAMS[/TD]
[TD]20120715[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]20122230325RAMS[/TD]
[TD]20130929[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]20122230325RAMS[/TD]
[TD]20120228[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2012
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]LookupCode[/TD]
[TD]BeginDate[/TD]
[TD]EndDate[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]20122230325RAMS[/TD]
[TD]20120101[/TD]
[TD]20120331[/TD]
[TD]100.99[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]20122230325RAMS[/TD]
[TD]20120401[/TD]
[TD]20120630[/TD]
[TD]105.87[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]20122230325RAMS[/TD]
[TD]20120701[/TD]
[TD]20121231[/TD]
[TD]109.12[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2013[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]LookupCode[/TD]
[TD]BeginDate[/TD]
[TD]EndDate[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]20132230325RAMS[/TD]
[TD]20130101[/TD]
[TD]20130331[/TD]
[TD]113.25[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]20132230325RAMS[/TD]
[TD]20130401[/TD]
[TD]20130630[/TD]
[TD]115.31[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]20132230325RAMS[/TD]
[TD]20123701[/TD]
[TD]20131231[/TD]
[TD]111.74[/TD]
[/TR]
</tbody>[/TABLE]

 

[kevatarvind]

not understanding what you want to do can you pls explain some more with desired result where you want to use formula and what ?

 

[Aladin Akyurek]

Invoice

D2, control+shift+enter, not just enter:

=INDEX(INDIRECT("'"&A2&"'!E2:E100),
  MATCH(1,IF(INDIRECT("'"&A2&"'!B2:B100)=B2,
  IF(C2>=INDIRECT("'"&A2&"'!C2:C100),
  IF(C2<=INDIRECT("'"&A2&"'!D2:D100),1))),0))

 

[JangLang]

I appreciate the help but it didn't work. The code stalls at the... MATCH(1,IF(INDIRECT("'" (emphasis on the "'" part)

I'll send you the sample sowrkbook if you want to view the error.

 

[Aladin Akyurek]

Invoice

D2, control+shift+enter, not just enter:

=INDEX(INDIRECT("'"&A2&"'!E2:E100),
  MATCH(1,IF(INDIRECT("'"&A2&"'!B2:B100)=B2,
  IF(C2>=INDIRECT("'"&A2&"'!C2:C100),
  IF(C2<=INDIRECT("'"&A2&"'!D2:D100),1))),0))

I appreciate the help but it didn't work. The code stalls at the... MATCH(1,IF(INDIRECT("'" (emphasis on the "'" part)

I'll send you the sample sowrkbook if you want to view the error.

Missing double quotes around the range specs...

=INDEX(INDIRECT("'"&A2&"'!E2:E100"),
  MATCH(1,IF(INDIRECT("'"&A2&"'!B2:B100")=B2,
  IF(C2>=INDIRECT("'"&A2&"'!C2:C100"),
   IF(C2<=INDIRECT("'"&A2&"'!D2:D100"),1))),0))

which must be confirmed with control+shift+enter, not just enter.

 

[JangLang]

I updated the code and it still stalls at the same place (" ' "). ?

 

[Aladin Akyurek]

Missing double quotes around the range specs...

=INDEX(INDIRECT("'"&A2&"'!E2:E100"),
  MATCH(1,IF(INDIRECT("'"&A2&"'!B2:B100")=B2,
  IF(C2>=INDIRECT("'"&A2&"'!C2:C100"),
   IF(C2<=INDIRECT("'"&A2&"'!D2:D100"),1))),0))

which must be confirmed with control+shift+enter, not just enter.

I updated the code and it still stalls at the same place (" ' "). ?

It works for me as is:

https://dl.dropboxusercontent.com/u/65698317/aaCondIndexMatchWithIdirect%20JangLang.xlsx

 

[JangLang]

You nailed it my friend! Thanks!!!!!!!

 

[Aladin Akyurek]

You nailed it my friend! Thanks!!!!!!!

You are welcome.