Excel: Searching for a substring in a column

Microsoft Excel 2002 (full product)
September 21, 2010 at 13:19:06
Specs: Windows Vista

I'm trying to write an Excel formula that, as I enter a value in column A1 in worksheet W1, will automatically search for the first 10 letters of that string (W1!A1) in the first 20 rows in worksheet W2 in column A. If it finds the value it will copy the value of W2!BX into W1!B1 (where "X" is the row in W2 where the substring was found.)

Does anyone have a clue how can I do this?


See More: Excel: Searching for a substring in a column

Report •

September 21, 2010 at 21:34:28
Do the values in W2!A1:A20 contain strings that are longer than the 10 characters you are searching for?

If so, try this:

=INDEX(W2!$A$1:$A$20, MATCH(LEFT($A$1, 10 ) & "*", W2!$A$1:$A$20, 0))

If W2!A1:A20 contain only 10 character strings, then you don't need the & "*".

MATCH will return the relative position of the value you are searching for from the Range specified - W2!$A$1:$A$20

LEFT($A$1, 10 ) & "*" tells MATCH to find the first 10 characters of A1 even if the cells in the range were are searching contain more than 10 characters.

INDEX returns the value from an array based on the row_num and column_num arguments. The row_num argument is provided by the MATCH function. We don't need column_num argument because the range is only 1 column wide.

Report •
Related Solutions

Ask Question