Multiple Results while Looking Up

[venunanduru]

While using "Vlookup" or "Index", if the "lookup_value" is matching with more than one cell, how to get all those matched results

 

[NARAYANK991]

Hi Venu ,


Can you check out the following article ?


http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/comment-page-4/#comment-50401


Narayan

 

[Montrey]

Wow! Great article Narayan

 

[venunanduru]

Not under stood, while implementing I am getting error even with the given example also, I want to display the exact matching results as well as nearby results also, for example in a telephone directory while searching with word "venu", then all the matching results to that word like "venu gopal", "venu rao", "venus" etc.. Should come in the result area vertically

 

[NARAYANK991]

Hi Venu ,


If you are not averse to using a helper column , then you can do the following :


1. Assume your data is in column A , say from A10:A20.


2. Assume your keyword , against which you want matches from the list in column A , is in A1.


3. Assume your helper column is B.


4. Enter the following formula in B10 , and copy it down till B20 :

[pre]

=COUNTIF(A10,$A$1&"*")
5.  Enter the following formula in C10 , and copy it down till C20 ; this is an array formula , to be entered using CTRL SHIFT ENTER :

=IFERROR(INDEX($A$10:$A$20,SMALL(IF($B$10:$B$20<>0,ROW($B$10:$B$20)-ROW($B$10)+1,ROW($B$20)),ROW(A1))),"")

[/pre]
The above will return only those entries which start with the text VENU ; if this text is embedded anywhere else in between other text such as in the word REVENUE , it will not be returned.


Narayan


P.S. The above is courtesy : http://www.dailydoseofexcel.com/archives/2004/06/07/simulating-a-like-function/

 

[Jazzy222]

Wonderful sharing thanks


<b>Matric Result</b>, <b>Matric Results</b>, <b>SSC Result</b> , <b>SSC Results</b>, <b>SSC Part 1 Result</b>, <b>SSC Part 2 Result</b>