 ##### 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.)

# non-Vlookup formula to capture data

0

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.

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

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

LarBar

### Discussion

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

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