Index Match with Wildcard Not Working

[Kas O]

Hello, I hope someone can help me with this please. I've tried Index/Match formulas but am challenged because I also need this to be a wildcard search. I have this column:

I would like to find any one of these items in column H's text and return it in the corresponding row in column I:

ie: It should find 2.60Ghz in each of these rows and return it in column I.
I think I've been staring at this too long, any help you can provide would be greatly appreciated!

Thanks,
-K

 

[jtakw]

Hi,

Please post samples using XL2BB so that we don't have to manually recreate your data, see my signature for where and how.
Is this what you mean.

Book3.xlsx
	E	F	G	H	I
1
2	1.60Ghz			abc 1.60Ghz xyz	1.60Ghz
3	1.80Ghz			abc 2.60Ghz xyz	2.60Ghz
4	1.90Ghz			abc 1.80Ghz xyz	1.80Ghz
5	2.30Ghz			abc 1.90Ghz xyz	1.90Ghz
6	2.60Ghz			abc 1.30Ghz xyz	Not Found
7	2.70Ghz			abc 2.30Ghz xyz	2.30Ghz
Sheet913

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(LOOKUP(2,1/SEARCH(E$2:E$7,H2),E$2:E$7),"Not Found")

 

[kweaver]

A variation (finds any GHz in the text) for some cell parallel to H2 and fill down:

=IF(ISNUMBER(SEARCH("?.??GHz",H2)),"Found: "&MID(H2,SEARCH("?.??GHz",H2),7),"Not Found.")

Book3
	E	F	G	H	I	J
2	1.60GHz			On the Insert tab, the galleries include items that are designed to coordinate with the overall look 1.80GHz of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.		Found: 1.80GHz
3	1.80GHz			Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas porttitor congue massa. Fusce 2.30GHZ posuere, magna sed pulvinar ultricies, purus lectus malesuada libero, sit amet commodo magna eros quis urna.		Found: 2.30GHZ
4	1.90GHz			Nunc viverra imperdiet enim. Fusce est. Vivamus a tellus.		Not Found.
5	2.30GHz			Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Proin pharetra nonummy pede 2.20GHz. Mauris et orci.		Found: 2.20GHz
Sheet1

Cell Formulas
Range	Formula
J2:J5	J2	=IF(ISNUMBER(SEARCH("?.??GHz",H2)),"Found: "&MID(H2,SEARCH("?.??GHz",H2),7),"Not Found.")

 

[jtakw]

@kweaver , Did you Actually type all that up in your Column H?

Or, may be, just copy/pasted any old text and inserted the Ghz values....

 

[kweaver]

@kweaver , Did you Actually type all that up in your Column H?

Or, may be, just copy/pasted any old text and inserted the Ghz values....

Heavens no. In Word, I used =LOREM() and copied & pasted paragraphs into Excel and inserted the GHz in random spots. GRIN.

 

[johnnyL]

@ Kas O, if all you are looking, for result wise, = xxxGHz then try the following in your I column cells ...

Example:
I2 formula =MID(H2,FIND("(",H2)+1,FIND(",",H2)-FIND("(",H2)-1)
I3 formula =MID(H3,FIND("(",H3)+1,FIND(",",H3)-FIND("(",H3)-1)

 

[Kas O]

Thank you kweaver this worked for me!

 

[jtakw]

Hello, I hope someone can help me with this please. I've tried Index/Match formulas but am challenged because I also need this to be a wildcard search. I have this column:

View attachment 37162
I would like to find any one of these items in column H's text and return it in the corresponding row in column I:

I understood it as you wanted to specifically check the List of Processor Mapping in Column E against the text strings in Column H, as you described.
But apparently Not, you just want to return Any ?GHz as a result, where Column E is Not a factor.

So, this might work.

Book3.xlsx
	H	I
2	abc 1.60GHz xyz	1.60GHz
3	abc 2.60GHz xyz	2.60GHz
4	abc 1.80GHz xyz	1.80GHz
5	abc 1.90GHz xyz	1.90GHz
6	abc 1.30GHz xyz	1.30GHz
7	abc 2.30GHz xyz	2.30GHz
Sheet913

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(MID(H2,FIND("GHz",H2)-4,7),"Not Found")