How to use Concatenate result in Vlookup

[rrosenwald]

I'm running Excel 2007

I have three related questions here.

I have a concatenate: CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6))

that yields a result like: $C$927:$F$1742

but: VLOOKUP(C1124,CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6)),4,FALSE)

yields: #N/A

althhough: VLOOKUP(C1124,$C$927:$F$1742,4,FALSE)

yields the correct result.

Question 1: How do I use the CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6)) inside a VLOOKUP?

Question 2: How would I do this within VBA?

Question 3: Would it be better to create a UDF Cell_Range() and how might that be written?

 

[jim may]

Select the cell that contains your =Concatenate() formula, then click the Formula Tab, then in the Formula auditing group "Evaluate Formula";
As you step thru what seems to be the problem?

 

[rrosenwald]

Thank you. I see my problem - Today() doesn't work real well with yesterday's data

 

[T. Valko]

I'm running Excel 2007

I have three related questions here.

I have a concatenate: CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6))

that yields a result like: $C$927:$F$1742

but: VLOOKUP(C1124,CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6)),4,FALSE)

yields: #N/A

althhough: VLOOKUP(C1124,$C$927:$F$1742,4,FALSE)

yields the correct result.

Question 1: How do I use the CONCATENATE(ADDRESS(MATCH(TODAY()-1,A:A,0)+1,3),":",ADDRESS(MATCH(TODAY(),A:A,0),6)) inside a VLOOKUP?

Question 2: How would I do this within VBA?

Question 3: Would it be better to create a UDF Cell_Range() and how might that be written?

This is a better way to define the range:

INDEX(C:C,MATCH(TODAY()-1,A:A,0)+1):INDEX(F:F,MATCH(TODAY(),A:A,0))

Then, your lookup formula would be:

=VLOOKUP(C1124,INDEX(C:C,MATCH(TODAY()-1,A:A,0)+1):INDEX(F:F,MATCH(TODAY(),A:A,0)),4,0)

Even better, use a cell to hold =TODAY()...

B1: =TODAY()

Then:

=VLOOKUP(C1124,INDEX(C:C,MATCH(B1-1,A:A,0)+1):INDEX(F:F,MATCH(B1,A:A,0)),4,0)

 

[rrosenwald]

Excellent! Thanks.

 

[T. Valko]

Excellent! Thanks.

You're welcome!