List using matching criterial of first 5 letters

Please attach a sample workbook with expected output for better understanding

Hello,

You can use this Array formula

=INDEX(Sheet1!$B$1:$B$3,SMALL(IF(ISNUMBER(SEARCH(Sheet1!$A$1:$A$3,$A1)),ROW(Sheet1!$A$1:$A$3)),ROW($A$1:$A1)))

You will have to hold Ctrl-Shift and hit Enter to enter the formula (if it's wrapped inside a { }, then you did it right)

And here is a small sample file with the formula (please prepare them beforehand next time)

Or

Formula:

=VLOOKUP(TRIM(LEFT(A1,FIND("-",A1)-1)),Sheet1!A:B,2,FALSE)

I will break the formula down and how I come up with it

First, I want to find which row has the text in A1, so I use this Array formula
IF(ISNUMBER(SEARCH(Sheet1!$A$1:$A$3,$A1)),ROW(Sheet1!$A$1:$A$3))

What it does is, SEARCH text A1 in every cell from A1 to A3. If it's found, SEARCH will return a number (position in which the text is found), and if it's not found, it will return an error (#VALUE), thus I added an ISNUMBER in front of it (if it returns a number, then TRUE, if not, FALSE). This IF function will then return the row number accordingly (For e.g text in A1 is found in row 1 of sheet1). You can read it like this
IF ( SEARCH ( text a1, list of text ) , list of rows )

The second part is SMALL. What this function does is that it will return the 1st, 2nd, ... smallest row among the rows from the IF formula (in case of duplicated data, A1 can be found on more than 1 row for example). The ROW formula at the end will decide whether it will be the 1st row (1st result found), 2nd or 3rd and so on basing on the row number where the formula is currently in.

The last part is INDEX, basically it will look into a column, then return the number on a specific row (For e.g, Text A1 found on row number 1, then INDEX should return value number 1 from B1 to B3)

And putting all of that together, you have

=INDEX(Sheet1!$B$1:$B$3,SMALL(IF(ISNUMBER(SEARCH(Sheet1!$A$1:$A$3,$A1)),ROW(Sheet1!$A$1:$A$3)),ROW($A$1:$A1)))

In case you don't have duplicated data, I suggest using the formula of Sixthsense, because Array formula is hard to edit and can be resource-consuming.

This sample file and the sample file you provided on the other day is reversed ...

Anyway, here's the sample file with the formula, see if this is what you are looking for.

I'm so sorry, for some reason I miss a -1 ...

Here is the sample file

I have explained it on post #7, with just a few change to the formula
Here is the formula in #7

=INDEX(Sheet1!$B$1:$B$3,SMALL(IF(ISNUMBER(SEARCH(Sheet1!$A$1:$A$3,$A1)),ROW(Sheet1!$A$1:$A$3)),ROW($A$1:$A1)))

And here is the one in the most recent attached file

=IFERROR(INDEX('Client sheet'!$C$2:$C$12,SMALL(IF(ISNUMBER(SEARCH(TRIM('Client sheet'!$A$2:$A$12),$A3)),ROW($A$2:$A$12)-1),1)),"")

If you ignore the IFERROR in the second formula, they are the same. The only changes I did were changing the cell reference, changing the worksheet, and add a -1 to the ROW formula in the IF because now you have a header row.