Hi, 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?

Thanks!

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_numandcolumn_numarguments. Therow_numargument is provided by the MATCH function. We don't needcolumn_numargument because the range is only 1 column wide.

Ask Your Question

Weekly Poll