• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return multiple items for single look up value and use wild card to count

K

kaushik03

Member
Hi all,


Please find the attached workbook here:


https://hotfile.com/dl/173883602/83ba0b2/Using_wild_card_and_return_multiple_values.xlsx.html


In col B, I have certain names repeated in rows and I want to get the count of these names and also return the corresponding values from respective columns.


Problem 1:


There are certain instances where a single cell contains more than 1 name (in that case the names are separated by ""). For example, "Annie" present in row 2,3,4 and 5. But at row 2 basically there are two names "Smitha/Annie".


As of now I have managed to get the count of names using wild card characters(*) in countif function.


=COUNTIF(B:B,"*Annie*") which gives me the count 4 (which is correct).


If I want to pass the cell reference(contains the name) instead of writing the name directly in the formula, the formula is not working.Actually, I want to keep all the names in a drop down cell and want to use that cell reference in the formula.

How can I do this?


Problem 2:


For each one of the name in col B, I want to return all the corresponding values from Co A and Col C. We can do this by using a combination of INDEX, SMALL, IF and ROW function.But again, since one cell may contains > 1 name, I am not able to construct the formula in order to work properly.


When I use this:

=IF($B$2:$B$8="Annie",ROW($B$2:$B$8)) (ctrl+shift+enter) I get :

{FALSE;3;4;5;FALSE;FALSE;FALSE}


So, for B2 I am getting "False" as it contains Smitha/Annie


Using wild card in above part of the formula, everywhere I am getting false.

Please tell me what tricks we need to follow here to get the job done.


Please understand, we do not have any provision to change the format of the data set.


Looking forward to your kind assistance.


Kaushik
 
Hi Kaushik ,


Try this , entered as an array formula , using CTRL SHIFT ENTER , to retrieve the IDs :


=IFERROR(INDEX($A$2:$A$8,SMALL(IF(IFERROR(FIND($E$1,$B$2:$B$8),0)>0,ROW($B$2:$B$8)-1),ROW(A1))),"")


You can use the same principle to retrieve the details.


Narayan
 
You are terrific Narayan...


Can you please also address my first problem?Is there any way we can pass the cell reference while using wild card in countif function or we have to use some other formula or technique to do this?


Kaushik
 
Hi Kaushik ,


Wild cards will work. Try this :


=COUNTIF(B:B,"*"&E1&"*")


As it stands , you can only look for the complete string of text in E1 , since the formula is not using the slash ( "/" ) as a delimiter ; so , for instance , if you enter Annie/Bharat in E1 , you will get 0 ; you might have expected 5 , but this will not be possible unless you change the formula.


Narayan
 
You must log in or register to reply here.
Back
Top