VLookup in column of text strings?

**leechy** · 06-28-2009, 02:40 PM

Hi Folks, could really use some help here.

I've got 100 rows of 20-letter text strings like the list below
How can i check if a specific number is the 8th character in one of text strings?
"DOES this number in cellA1, once converted to text, match Mid(8,2) in any of the strings in this column range?

eg: trying to lookup if the number 10 does appear in this list (shortened)

20-letter text strings below:
CQN CN 8 C48 Equity
CQN CN 9 C50 Equity
CQN CN 10 C50 Equity <-- here it is!
CQN CN 11 C50 Equity

many thanks to all. L.

**martindwilson** · 06-28-2009, 03:07 PM

a few options
=IF(ISNUMBER(FIND("10 ",A2)),"found","not found")
=IF(MID(A2,8,2)="10","found","not found")
=VLOOKUP("*10 *",A2:C5,2, FALSE)
or array entered with CTRL+SHIFT+ENTER
=MATCH("10",MID(A2:A5,8,2),0)

**Buzzpipaluk** · 03-22-2010, 06:57 AM

How would this be extended to look for one of 2 characters in the same place in a text string?
i.e. look for a J or F in the 9th position of a text string and return Y or N

Thanks

**NBVC** · 03-22-2010, 08:27 AM

Buzzpipaluk,

If you are not providing a solution or querying the OP specifically about the original question, then do not ask a question in the thread of another member. Please start your own thread.. if relevant, add a link to this one.