Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

[nickwood1066]

Hi,

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

Any idea?

Any help much appreciated!

Best regards

Nick

 

[iazeemkhan]

Example:

=SUMPRODUCT(--ISNUMBER(SEARCH(K596:CZ596,B596)))=COUNTA(K596:CZ596)

Sir, Instead of writing "Same" can I give cell reference, as I want to search multiple text from different cell and if those text found in one cell that is the main source is it possible. Please provide me help with formula.

 

[iazeemkhan]

Example:

=SUMPRODUCT(--ISNUMBER(SEARCH(K596:CZ596,B596)))=COUNTA(K596:CZ596)

Sir, Instead of writing search name "text" can I give cell reference, as mentioned above, as I want to search multiple text from different cell and if those text found in one cell then it will be TRUE else false. Please provide me help with formula.

Thank you.

 

[Aladin Akyurek]

Example:

=SUMPRODUCT(--ISNUMBER(SEARCH(K596:CZ596,B596)))=COUNTA(K596:CZ596)

Sir, Instead of writing search name "text" can I give cell reference, as mentioned above, as I want to search multiple text from different cell and if those text found in one cell then it will be TRUE else false. Please provide me help with formula.

Thank you.

Book1
	A	B	C	D	E
1	Description	Search 1	Search 2	Search 3
2	CENTRAL BATTERY SYSTEM AND EMERGENCY LIGHTING INSTALLATION; EQUIPMENT; Switchgear and equipment,	Central battery	LIGHTING	Switch	TRUE
3
Sheet1

E2:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&B2:D2&" "," "&$A2&" ")))

 

[iazeemkhan]

Dear Mr. Aladin Akyurek,

At the outset I am very grateful to you for your kind help. I have to get the following ; please help me in this command.

I have to pull out all words and write in one column which is there in below sentence (Search from List)

Column A = John has working knowledge of Oracle and Excel

I have following list;

Oracle

MySQL

Toad

Excel

John

Raju

Rajesh

Hardware

Software

DB

MDB

SMDB

ESMDB

FESMDB

/f

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Dear Mr. Aladin Akyurek,

At the outset I am very grateful to you for your kind help. I have to get the following ; please help me in this command.

I have to pull out all words and write in one column which is there in below sentence (Search from List)

Column A = John has working knowledge of Oracle and Excel

I have following list;

Oracle

MySQL

Toad

Excel

John

Raju

Rajesh

Hardware

Software

DB

MDB

SMDB

ESMDB

FESMDB

/f

<tbody>
</tbody>

Book1
	A	B	C	D	E	F
1
2	John has working knowledge of Oracle and Excel					DB
3						ESMDB
4	Excel					Excel
5	John					FESMDB
6	Oracle					Hardware
7						John
8						MDB
9						MySQL
10						Oracle
11						Rajesh
12						Raju
13						SMDB
14						Software
15						Toad
Sheet1

Name the sorted list in F2:F15 List. Note that the sorting must be in ascending order.

In A4 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(List,SMALL(IF(ISNUMBER(SEARCH(" "&List&" "," "&$A$2&" ")),ROW(List)-ROW(INDEX(List,1,1))+1),ROWS($1:1))),"")

 

[iazeemkhan]

Dear Sir,

Many thanks for your solution.

Thanks a lot......

 

[iazeemkhan]

Dear Sir,

Request you yo please send me a solution for below;

Sheet 1

1) There is a List of Component Names in A column
2) From column B to column ALZ there are item names in each column which comes under component in Column A.

Sheet 2

Column A content Descriptions, Column B will be Result Column

Now What I need

I want to search Item Names in Description (Column A Sheet 2),if found get the Component Name of that Item Name in Result column B (Sheet 2)


SHEET 1

COMPONENT NAMES IN COLUMN A
ITEM NAMES (which comes under Column A) FROM COLUMN B to XDZ (Remaining Item names will be added in main data, below is just a sample)



Component
Name I T E M N A M E S .................................................................................

A	B	C	D	E	F	G	F	G
HV Switchgear	MV Panels	MV Switchgear	11 KV Switchgear	RMU	MVS	MLT
Access control	Card Reader	Push button for exit	Door contact/holder	Mortise Lock	Input Module	Door controller
Fire alarm	Heat Detector	Multi Sensor	Smoke detector	Duct Smoke Detector	Sounder horn	Strobe Light
PAVA	remote paging microphone	emergency panel	digital event recorder	CD player	tuner	overriding relay
Active components	IP Telephony Handsets	PABX	Wireless controller	-	-	-

<tbody>
</tbody>

SHEET 2

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player

<tbody>
</tbody>

===================================================
SAMPLE FINAL RESULT (REFERENCE ONLY)

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels	HV Switchgear
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge	Access control
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector	Fire alarm
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player	PAVA

<tbody>
</tbody>

Thank you Sir, I tried to explain but If you have any query, please let me know.

 

[Aladin Akyurek]

Dear Sir,

Many thanks for your solution.

Thanks a lot......

You are welcome.

 

[Aladin Akyurek]

Dear Sir,

Request you yo please send me a solution for below;

Sheet 1

1) There is a List of Component Names in A column
2) From column B to column ALZ there are item names in each column which comes under component in Column A.

Sheet 2

Column A content Descriptions, Column B will be Result Column

Now What I need

I want to search Item Names in Description (Column A Sheet 2),if found get the Component Name of that Item Name in Result column B (Sheet 2)


SHEET 1

COMPONENT NAMES IN COLUMN A
ITEM NAMES (which comes under Column A) FROM COLUMN B to XDZ (Remaining Item names will be added in main data, below is just a sample)



Component
Name I T E M N A M E S .................................................................................

A	B	C	D	E	F	G	F	G
HV Switchgear	MV Panels	MV Switchgear	11 KV Switchgear	RMU	MVS	MLT
Access control	Card Reader	Push button for exit	Door contact/holder	Mortise Lock	Input Module	Door controller
Fire alarm	Heat Detector	Multi Sensor	Smoke detector	Duct Smoke Detector	Sounder horn	Strobe Light
PAVA	remote paging microphone	emergency panel	digital event recorder	CD player	tuner	overriding relay
Active components	IP Telephony Handsets	PABX	Wireless controller	-	-	-

<tbody>
</tbody>

SHEET 2

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player

<tbody>
</tbody>

===================================================
SAMPLE FINAL RESULT (REFERENCE ONLY)

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels	HV Switchgear
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge	Access control
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector	Fire alarm
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player	PAVA

<tbody>
</tbody>

Thank you Sir, I tried to explain but If you have any query, please let me know.

Please try to continue this one in https://www.mrexcel.com/forum/excel-questions/893600-extract-multiple-keywords-text-string-3.html. Thank you.

 

[iazeemkhan]

Ok Sir,

Thanks a lot.