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.)
Hello Sir,
I applied vlookup multiple rows with the same lookup value with index farmulla, my same lookup values are more than 3 i.e. if it if 10, then it shows only 1st value as a result and then #num error. rest of 9 values are not shows. i copied the farmulla 10 times... but results not came.
I attached a sample file, where i want subdealers of rohit enterprises, to be shown 1 by 1
pls help me with the same
Ok I think I understand you. You want to return every instance of a match.
You need to use an array formula to do that and if you want to remove the #NUM error, you need to surround the formula with an IFERROR function.
Here is one that will work in your example:
=IFERROR(INDEX($B$1:$B$7, SMALL(IF($B$15=$A$1:$A$7, ROW($A$1:$A$7)-ROW($A$1)+1), ROW(1:1))),"")Make sure to input it into Excel using Ctrl + Shift + Enter