Vlookup formula Help


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

Post Edited
Title: Title was not descriptive.


You need to include the sample formula or, even better, a sample file. The description alone is not enough to understand the problem.
don (rep: 1247) Jan 10, '17 at 12:11 pm
I attached a sample file.

Thank You
Rohit Jan 11, '17 at 2:13 am
Add to Discussion



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


Answer the Question

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