Index match looking using part of a string

Hi

I successfully use INDEX/SMALL formulas to extract all the rows that have a cell containing a value equal to that in a source cell -

For example - I give my customers a 'shortname' (Smi for Smiths or Smithson or Smithy etc)

for example

=IFERROR(INDEX(dgts_current!$A$3:$N$10000,SMALL(IF(dgts_current!$E$3:$E$10000=$C$23,ROW(dgts_current!$E$3:$E$10000)),ROW(1:1))-2,1),"")

Where C23 is my source cell

I can then list all of those customers who have a shortname of Smi

What I'm trying to do now is to find a way of missing out the three-letter shortname column

So if I type 'Sm' into a source cell I want to generate a list of all customers whose name starts with 'Sm'

I've found one or two near-misses but nothing that does exactly what I'm after - any ideas?

Sure. If you create a small, representative sample of your data along with the desired output of the formula/s, we should be able to solve this for you in no time.

Originally Posted by Andy Swain

Hi

I successfully use INDEX/SMALL formulas to extract all the rows that have a cell containing a value equal to that in a source cell -

For example - I give my customers a 'shortname' (Smi for Smiths or Smithson or Smithy etc)

for example

=IFERROR(INDEX(dgts_current!$A$3:$N$10000,SMALL(IF(dgts_current!$E$3:$E$10000=$C$23,ROW(dgts_current!$E$3:$E$10000)),ROW(1:1))-2,1),"")

Where C23 is my source cell

I can then list all of those customers who have a shortname of Smi

What I'm trying to do now is to find a way of missing out the three-letter shortname column

So if I type 'Sm' into a source cell I want to generate a list of all customers whose name starts with 'Sm'

I've found one or two near-misses but nothing that does exactly what I'm after - any ideas?

Please attach your sheet.

Hi - I've uploaded a file called Testnames.xlsm with a short example list of names in sheet2 and example entry in sheet1 - thanks

With "PE" in A3 of Sheet1, in A6 of Sheet1, try this:

=IFERROR(INDEX(Sheet2!A$2:A$10,SMALL(IF(LEFT(Sheet2!A$2:A$10,2)=A$3,ROW(Sheet2!A$2:A$10)-(ROW(Sheet2!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

Copy the formula down as far as needed.

That works with 2 letters (PE) will it work if I narrowed the range down to PET or PETE?

Sure, just change 2 to LEN(A$3). That is,

=IFERROR(INDEX(Sheet2!A$2:A$10,SMALL(IF(LEFT(Sheet2!A$2:A$10,LEN(A$3))=A$3,ROW(Sheet2!A$2:A$10)-(ROW(Sheet2!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

Many thanks - works a treat!

You're welcome. Happy to help.

Thanks for the rep!

If that solved your question, please mark this thread as SOLVED.

Sorry - one last thing - is it possible to search for PE or PET etc (fragment?) within the string (ie customer names)?

Sure. That would be this:

=IFERROR(INDEX(Sheet2!A$2:A$10,SMALL(IF(ISNUMBER(SEARCH(A$3,Sheet2!A$2:A$10)),ROW(Sheet2!A$2:A$10)-(ROW(Sheet2!A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

Many thanks - all sorted!