using INDIRECT with VLOOKUP

[Msgjazz]

my current formula is
=VLOOKUP($A5,'[2017-07 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498,6,FALSE).

I would like for the "2017-07" to refer to any month that is entered in cell M2.

So if M2 changers to 2017-09 then the formular would read
=VLOOKUP($A5,'[2017-09 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498,6,FALSE).

Can't get the INDIRECT and CONCATENATE sequence right.

Thanks

 

[Jonmo1]

What 'Exactly' do you enter in M2 ?
Is it an actual Date, or just a text string ?

What does this return, true or false
=ISNUMBER(M2)

?

 

[Jonmo1]

If the ISNUMBER is FALSE, try
=VLOOKUP($A5,INDIRECT("'["&M2&" - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498"),6,FALSE)

If the ISNUMBER is TRUE, try
=VLOOKUP($A5,INDIRECT("'["&TEXT(M2,"yyyy-mm")&" - Caseload Listing Details.xlsx]Caseload Listing Detail'!$B$2:$G$34498"),6,FALSE)

 

[Msgjazz]

It's text formatted as General. they would just type 2017-07 or 2017-10.... Not date format

 

[Msgjazz]

Thanks. Works perfectly. Thanks to people like yourself, this site is the best.

 

[Msgjazz]

I hate to ask, but can this also work with INDEX?

=INDEX(INDIRECT("'["&M2&" - Caseload Listing Details.xlsx]Caseload Listing Detail'!$D$2:$D$34914,MATCH(A5,'["&M2&" - Caseload Listing Details.xlsx]Caseload Listing Detail'!$E$2:$E$34914,0)"))

Currently it's:
=INDEX('[2017-08 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$D$2:$D$34914,MATCH(A5,'[2017-08 - Caseload Listing Details.xlsx]Caseload Listing Detail'!$E$2:$E$34914,0)). Cell M2 is still where the text is entered.

 

[Jonmo1]

You're welcome.

You'll need to separate indirect funtions.
Indirect is used to create a RANGE, not a function..

so you do like
=INDEX(INDIRECT(...IndexRange...),MATCH(lookupvalue,INDIRECT(...MatchRange...),0))

 

[Greg Truby]

Yes, the logic would be the same as with VLOOKUP()

But allow me to express a couple of concerns:
(a) INDIRECT will only work on OPEN workbooks. If the workbook you're planning on referencing is closed, INDIRECT() will return an error. [I think it'll be a #REF error, but didn't test.]
(b) Your formula is doing a VLOOKUP() or an INDEX(MATCH()) into the ranges from rows 2 to 34914. I would be more'n a mite skittish 'bout that. I'd wager that as you move from month to month the size of the range you want to look in and fetch from is going to grow or shrink. I would consider using dynamic named ranges in your formulae or else naming the ranges in the external workbooks.

 

[Msgjazz]

Thanks. The workbook will be close, so I'll leave it as VLOOKUP. I've also extended the range to 37000 which is more that the max in any given month. How would I go about naming the range of B$2:$G$34498? When naming it, would I just specify the columns B:G? [Refers to: ='Caseload Listing Detail'!$B:$G]

Thanks

 

[Jonmo1]

The workbook will be close, so I'll leave it as VLOOKUP

As Greg said,
If the workbook is closed, Indirect will not work.

There is an addin called morefunc that works like indirect but will allow closed workbooks.
There's a link to it here, not sure if it's still accurate.
https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html