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!
LarBar
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
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
Answers
0
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))),"")