Reputation: 1
Is there any way to declare a range in an Excel formula and have the value returned by MATCH() as the row number?
Example:
=VLOOKUP(C3,'Sheet2'!B2:C30000,2,FALSE)
What I want is the formula equivalent of VBA's "B" & [
MATCH() return] & ":C30000"
.
I am not permitted to use VBA.
Upvotes: 0
Views: 67
Reputation: 152660
Use Index, INDIRECT and OFFSET are volatile and should be avoided when possible.
=VLOOKUP(C3,INDEX('Sheet2'!B:B,MATCH(...)):'Sheet2'!C30000,2,FALSE)
Change the MATCH(...)
to your Match formula.
Upvotes: 1