Is there a formula that will search for nonspecific values and return a match?

[Yodaman1997]

I have a list of words that follow a specific pattern: (V for vowel) VtVsV, VbVbV, VtVdV, and other silly patterns.
Is there a formula that will search for words that have a similar pattern. Say i have "atasa" and somewhere I have a the word "ababa" but I don't know where. What would I do to find if I had it?

Thank you in advance.

[daffodil11]

I can't think of anyway to do this without code.

Here's a quick loop that highlights all cells yellow in the range that match the same pattern.

To use this, simply go to Excel, hit Alt+F11, Insert->Module, and paste the code below. Be sure to change the two ranges as notated below. The first range holds the cell you want to compare, and the second range contains all the cells you want to search. Then close that window.

You can call the macro from normal spreadsheet world with Alt+F8.

Please Login or Register  to view this content.

[István Hirsch]

Try this formula (enter into B2, the pattern list is in D2:D9):

=IFERROR(INDEX(D$2:D$8,MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"a","V"),"e","V"),"i","V"),"o","V"),"u","V"),D$2:D$8,0)),"")

VsVtV.jpg

[Yodaman1997]

I'm sorry the formula isn't working for me. I have pasted the formula into B2, copied the words down the A1:A10 column, and even the words through A1:D1.

Can you help?

[Yodaman1997]

I'm sorry, István Hirsch, the formula isn't working for me. I have pasted the formula into B2, copied the words down the A1:A10 column, and even the words through A1:D1.

Can you help?

[István Hirsch]

The argument separator in your Excel is "," or ";"? If the latter, substitute each "," in the formula with ";" and let me know if it helps.

[István Hirsch]

The argument separator in your Excel is "," or ";"? If the latter, substitute each "," in the formula with ";" and let me know if it helps.
P.S.: change D$8 to D$9 (at two places in the formula).

[FlameRetired]

I may have interpreted the question differently. This is what I came up with:
Copy and paste into B1 and fill down.

Please Login or Register  to view this content.

Does this do what you want?

I edited this post. It had an error in the formula.