Dynamic Ranges/Array in Vlookup

[JayB0730]

Hello,

I have a range of sells as part of my Array lookup: A2:D6; However, I would like to make this array dynamic so if additional rows are created (and if possible, additional columns), the range updates automatically.

Is this doable without VBA?

TIA
J

 

[Gerald Higgins]

If you insert a new row, say at row 4, then the formula should update itself automatically, to refer to A2:D7.

 

[Aladin Akyurek]

[1]

=VLOOKUP(LookupValue,A:D,MATCH(Header,INDEX(A:D,1,0),0),match-type) where match-type is either 0 or 1.


[2]

Select A1:D6 and activate Insert | Table. This "forces" you of course to work with the so-called structured references.


[3] Set up a dynamic range locally in the formula sheet. Suppose that column A of A2:D6 is text...

In say E1 enter:

=MATCH(REPT("z",255),A:A)

Given E1, you can rewrite the VLOOKUP we have started with as:

=VLOOKUP(LookupValue,A:INDEX(D:D,$E$1),MATCH(Header,$A$1:$D$1,0),match-type)


[4] Set up a named dynamic range globally using the Name Manager. Suppose again that column A of A2:D6 is text...

Define Lrow in Formulas | Name Manager as referring to:

=MATCH(REPT("z",255),Sheet1!$A:$A)

Adjust the sheet name to suit.

Define LTable in Name Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$D:$D,Lrow)

Now we can have:

=VLOOKUP(LookupValue,LTable,column-index,match-type)

 

[JayB0730]

Thank you Aladin. This was extremely helpful!

 

[Aladin Akyurek]

Thank you Aladin. This was extremely helpful!

You are welcome.