Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

non-Vlookup formula to capture data


I watched your video using a formual to capture data anywhere in a worksheet, called
"Vlookup to Return All Matches"Here's the formula
=IFERROR(INDEX($B$2:$B$8, SMALL(IF($D$2=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"")  "
I'm wonder if there's a way to use column names instead of
hardcoded references.
Using your spreadsheet use:
"Colors" instead of $A$2:$A$8
"Thing" instead of $B$2:$B$8

These names would contain the range values.

I have many spreadsheets that I use column names for and
whenever new data is added a macro is run to reset these column
ranges to include the newly added data rows.
For example
I have a worksheet that has data in rows 2 thru 500 so a
name is set to 'Colors' $B$2:$B$500
When I add new rows, say 10 rows of data, the macro will
change the range for 'Colors' to $B$2:$B$510

I've been trying to set this up using your formula but haven't
had any luck.

Any ideas would be helpful.

BTW, I appreciate your youtube videos, they have help me a lot!




I'm glad you liked the video!) Try out Variatus' solution and see how it works for you. Also, If you use Tables (Insert>Table) you shouldn't have to use a macro to update the named range when you add data and you can refer to the range simply using Table syntax.
don (rep: 1989) May 30, '20 at 2:11 am
Add to Discussion



Excel's default syntax for addressing ranges is to address them by name. So, your question, really, is a non-question. In the absence of an assigned name, Excel makes one up from the range's coordinates. Therefore $B$2:$B$8 is a name, and therefore that name can be replaced with another one you have defined yourself, for example "Color".

=IFERROR(INDEX(Thing, SMALL(IF($D$2=Color, ROW(Color)-ROW($A$2)+1), ROW(1:1))),"")

Answer the Question

You must create an account to use the forum. Create an Account or Login