Issue with vlookup and indirect

[Michal_PVP]

Hey Guys,

This is proabaly a popular question but I have a problem definining a range for the 'vlookup' function, where the range is defined by the 'indirect' function referencing a different worksheet.

The formula is as follows:

=VLOOKUP(EX2,INDIRECT("Add On Matrix'!B"&'Add On Matrix'!AL13&":'Add On Matrix'!AC"&'Add On Matrix'!AL14),MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,FALSE)

It collapses because of the range for the function, and I get a #REF! error in the end.

Many thanks in advance!

 

[Aladin Akyurek]

Hey Guys,

This is proabaly a popular question but I have a problem definining a range for the 'vlookup' function, where the range is defined by the 'indirect' function referencing a different worksheet.

The formula is as follows:

=VLOOKUP(EX2,INDIRECT("Add On Matrix'!B"&'Add On Matrix'!AL13&":'Add On Matrix'!AC"&'Add On Matrix'!AL14),MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,FALSE)

It collapses because of the range for the function, and I get a #REF! error in the end.

Many thanks in advance!

Try to post the formula without INDIRECT and add which parts must be fed to INDIRECT.

 

[Michal_PVP]

Sure, here goes:

=VLOOKUP(EX2,Range Given by Indirect,MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,FALSE)

I ma trying to look up a value from an array imported to a separate worksheet: "Add On Matrix".

Now the range, in question is B208:AC246 in the worksheet in question and the values of the relevant range boundaries are given in cells: AL13 = 208 and AL14=246 and these are defined by match functions which find the necessary rows (range boundaries) as the array changes on a weekly basis.
Now the indirect function comes in which is supposed to define the range by referencing the worksheet "Add On Matrix":

INDIRECT("Add On Matrix'!B"&'Add On Matrix'!AL13&":'Add On Matrix'!AC"&'Add On Matrix'!AL14)

That's where the problem occurs as I get a #REF! error so I'm guessing I missed something in the INDIRECT formula.

Thanks for your help.

 

[bebo021999]

=VLOOKUP(EX2,INDIRECT("Add On Matrix'!B"&'Add On Matrix'!AL13&":'Add On Matrix'!AC"&'Add On Matrix'!AL14),MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,FALSE)

Try to replace upper green part with below:

INDIRECT("[COLOR=red][B]'[/B][/COLOR]Add On Matrix'!B"&'Add On Matrix'!AL13&"[B][COLOR=red]:AC[/COLOR][/B]"&'Add On Matrix'!AL14)

 

[Michal_PVP]

That's works perfectly, thanks a lot!

 

[Aladin Akyurek]

Sure, here goes:

=VLOOKUP(EX2,Range Given by Indirect,MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,FALSE)

I ma trying to look up a value from an array imported to a separate worksheet: "Add On Matrix".

Now the range, in question is B208:AC246 in the worksheet in question and the values of the relevant range boundaries are given in cells: AL13 = 208 and AL14=246 and these are defined by match functions which find the necessary rows (range boundaries) as the array changes on a weekly basis.
Now the indirect function comes in which is supposed to define the range by referencing the worksheet "Add On Matrix":

INDIRECT("Add On Matrix'!B"&'Add On Matrix'!AL13&":'Add On Matrix'!AC"&'Add On Matrix'!AL14)

That's where the problem occurs as I get a #REF! error so I'm guessing I missed something in the INDIRECT formula.

Thanks for your help.

Try...

=VLOOKUP(EX2,INDEX('Add On Matrix'!$B:$B,$AL$13):INDEX('Add On Matrix'!$AC:$AC,$AL$14),
    MATCH(ER2,'Add On Matrix'!D2:AC2,0)+2,0)