CODEWITHSUNDEEP
excelexcel-formula
AMEK
AMEK

Reputation: 1

Separating Column number from Row letter in an Excel formula

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

Answers (1)

Scott Craner
Scott Craner

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

Related Questions