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

Year	LookupCode	PurchaseDate	Price
2012	20122230325RAMS	20120715
2013	20122230325RAMS	20130929
2012	20122230325RAMS	20120228

<tbody>
</tbody>

Sheet 2012

Year	LookupCode	BeginDate	EndDate	Price
2012	20122230325RAMS	20120101	20120331	100.99
2012	20122230325RAMS	20120401	20120630	105.87
2012	20122230325RAMS	20120701	20121231	109.12

<tbody>
</tbody>

Sheet 2013

Year	LookupCode	BeginDate	EndDate	Price
2013	20132230325RAMS	20130101	20130331	113.25
2013	20132230325RAMS	20130401	20130630	115.31
2013	20132230325RAMS	20123701	20131231	111.74

<tbody>
</tbody>

 

[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.