Unable to use SUBSTITUTE(ADDRESS(MATCH))) combination as a range in a LOOKUP function in Excel

1
votes

I have an Excel sheet containing a matrix of [Year & Week number] in rows and [name of Employees] in row1 giving values of available weekly hours.

Excel_Query_Data

I am able to successfully use the Excel formula 

=LOOKUP(2,1/((A:A=2018)*(B:B=31)),D:D)

at cell F2 for a reverse lookup. It gives the result 15 correctly.

However, I wanted to replace range D:D in the above formula to a dynamic range by identifying the column when the employee is known.

So I tried replacing by the formula

SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","")

This portion of the formula works and gives D:D for Employee2. This is shown to work in cell F4.

But the revised formula gives an error of #Value! at cell F6.

It says "A value used in the formula is of the wrong data type." The revised formula which does not work is:

=LOOKUP(2,1/((A:A=2018)*(B:B=31)),SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1",""))

I hope someone can help show me where I am making an error in trying to replace range D:D in the LOOKUP formula with the combination of SUBSTITUTE, ADDRESS & MATCH functions.

Thanks to all trying to help in advance.

1
excellookupreverse-lookup

1 Answers

1
votes

You cannot just plug a string, which is what SUBSTITUTE returns into a formula.

You can use INDERICT to turn the string into a viable reference:

INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1","") &":"&SUBSTITUTE(ADDRESS(1,MATCH("Employee2",1:1,0),4),"1",""))

But both INDIRECT and ADDRESS are volatile.

Use instead INDEX to return the correct column:

INDEX(A:AAA,0,MATCH("Employee2",1:1,0))

So your formula is:

=LOOKUP(2,1/((A:A=2018)*(B:B=31)),INDEX(A:AAA,0,MATCH("Employee2",1:1,0)))