2

I'm trying to tweak this piece of code I found in a sample spreadsheet online but I can't quite get my head around it.

The original spreadsheet basically does an INDEX/MATCH based on a user-defined lookup and lists the matches neatly in a concatenated list. The sample spreadsheet's output looks like this:

https://i.sstatic.net/DyahB.png - Sample Excel Output (Note how there are no gaps between the first and second matches)

The underlying algorithm is:

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

Now, I want the lookup to instead retrieve PARTIAL matches, and in addition, generate the outputs horizontally like so:

https://i.sstatic.net/ShED0.png - Output is generated horizontally based on partial matches

I'm not sure how I would go about doing this. It seems like I would somehow try and change the IF condition to return true on partial matches but I can't get my head around it. Please help!

Improve this question

2 Answers 2

Reset to default
3

Assuming by "partial match" you mean text that starts with the value in L1 then use this formula in N1

=IFERROR(INDEX($I$2:$I$8,SMALL(IF(LEFT($H$2:$H$8,LEN($L$1))=$L$1,ROW($I$2:$I$8)-ROW($I$2)+1),COLUMNS($N1:N1))),"")

confirm with CTRL+SHIFT+ENTER and copy across

For a match anywhere in the text you can use this version

=IFERROR(INDEX($I$2:$I$8,SMALL(IF(ISNUMBER(SEARCH($L$1,$H$2:$H$8)),ROW($I$2:$I$8)-ROW($I$2)+1),COLUMNS($N1:N1))),"")

Neither formula is case-sensitive, although you can easily make the latter so by changing SEARCH to FIND

Use of IFERROR function means you don't need repetition for error handling - needs Excel 2007 or later version

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Building on Barry's code a little, I needed to make a few tweaks for my own use (current project I have at work).

Tweaks I made:

  1. Returning the cell that matches my search criteria in my index
  2. Making the cell draggable in two dimensions so I could index multiple columns for specific information
  3. Making the "nth" counter vertical instead of horizontal (as my application is a database of sorts, and each column is a separate entry. At the top of each column is 5 rows populated based on the search term [in my case, the store number])

The final result is:

=IFERROR(INDEX(A$8:A$295,SMALL(IF(ISNUMBER(SEARCH('Store History'!$F$2,A$8:A$295)),ROW(A$8:A$295)-ROW(A$8)+1),ROWS(A$2:A2))),"")

It is worth repeating that this is an array formula and needs to be entered using CTRL+SHIFT+ENTER

This is placed in cell A2 and is dragged both vertically and horizontally (horizontally in my case is ever expanding as I add more entries into my database).

My purpose for adding this comment (even though it is a long inactive thread) is to try and make this a more relevant search result on Google for "excel index match partial strings with multiple results" or variations of that. It took me hours of searching to find this solution, and it is extremely functional and elegant. My thanks to the OP and especially to Barry for his code!!

Improve this answer

3 Comments

It is kind of unclear to me (granted, I do lack domain knowledge here) whether this is commentary on the other answer, an answer to the original question, or an answer to another, more general question. If it's the latter, posting a new question that clearly states the more general requirements and self-answering it would be the best action.
I didn't realize we were allowed to self-answer. I'll do that. Thank you!
@BaummitAugen I've posted a question and reply here: link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.