Dynamic Ranges/Array in Vlookup

JayB0730

Board Regular
Joined
Oct 22, 2014
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If you insert a new row, say at row 4, then the formula should update itself automatically, to refer to A2:D7.
 
Upvote 0
[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)
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top