Hi,

I have this in my spreadsheet excel 2003

worksheet 1 worksheet 2 worksheet 3

column A column A column B

Civil Isometrics Turbines
Mechanical Vendors Electrical
Electrical Scope Waste Managemet
Instrument Electrical QMS
.......ans so on

I want to find the word "Electrical" from my 3worksheets and the results
will appear next to the cell of worksheet 1 column A which is column B and C,
like this

w o r k s h e e t 1
column A column B column C

Civil
Mechanical
Electrical Electrical Electrical
Instrument

how would write my formula?

thanks for any help, I appreciate

Hi,

Assuming Electrical is in A4 of sheet1, put thin in B4

=VLOOKUP(A4,Sheet2!A$2:A$100,1,FALSE)

similar idea for sheet3.

You haven't told us how you want to handle it if the item is not on sheet2....

could use
=IF(ISNA(VLOOKUP(A4,Sheet2!A$2:A$100,1,FALSE)),"", VLOOKUP(A4,Sheet2!A$2:A$100,1,FALSE))


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Denver" wrote:

Hi,

I have this in my spreadsheet excel 2003

worksheet 1 worksheet 2 worksheet 3

column A column A column B

Civil Isometrics Turbines
Mechanical Vendors Electrical
Electrical Scope Waste Managemet
Instrument Electrical QMS
......ans so on

I want to find the word "Electrical" from my 3worksheets and the results
will appear next to the cell of worksheet 1 column A which is column B and C,
like this

w o r k s h e e t 1
column A column B column C

Civil
Mechanical
Electrical Electrical Electrical
Instrument

how would write my formula?

thanks for any help, I appreciate

Hi Denver

Assuming you have headers in row 1 of Sheet1. In worksheet 1 Column B;

In B2
=VLOOKUP(A2,Sheet2!A$2:A$1000,1,FALSE)
If you are looking to find the entry having word 'electrical' say for
example in Sheet2 if you want to retrive the entry electrical equipment use
the below formula
=VLOOKUP("*" & A2 & "*",Sheet2!A$2:A$1000,1,FALSE)


Similar to the above in Sheet1 ColC cell C2
ColB In B2
=VLOOKUP(A2,Sheet3!A$2:A$1000,1,FALSE)
OR
=VLOOKUP("*" & A2 & "*",Sheet3!A$2:A$1000,1,FALSE)


If this post helps click Yes
---------------
Jacob Skaria


"Denver" wrote:

Hi,

I have this in my spreadsheet excel 2003

worksheet 1 worksheet 2 worksheet 3

column A column A column B

Civil Isometrics Turbines
Mechanical Vendors Electrical
Electrical Scope Waste Managemet
Instrument Electrical QMS
......ans so on

I want to find the word "Electrical" from my 3worksheets and the results
will appear next to the cell of worksheet 1 column A which is column B and C,
like this

w o r k s h e e t 1
column A column B column C

Civil
Mechanical
Electrical Electrical Electrical
Instrument

how would write my formula?

thanks for any help, I appreciate

Thank you Jacob Skaria and Shane Devenshire
it is very helpful to me
"Jacob Skaria" wrote:

Hi Denver

Assuming you have headers in row 1 of Sheet1. In worksheet 1 Column B;

In B2
=VLOOKUP(A2,Sheet2!A$2:A$1000,1,FALSE)
If you are looking to find the entry having word 'electrical' say for
example in Sheet2 if you want to retrive the entry electrical equipment use
the below formula
=VLOOKUP("*" & A2 & "*",Sheet2!A$2:A$1000,1,FALSE)


Similar to the above in Sheet1 ColC cell C2
ColB In B2
=VLOOKUP(A2,Sheet3!A$2:A$1000,1,FALSE)
OR
=VLOOKUP("*" & A2 & "*",Sheet3!A$2:A$1000,1,FALSE)


If this post helps click Yes
---------------
Jacob Skaria


"Denver" wrote:

Hi,

I have this in my spreadsheet excel 2003

worksheet 1 worksheet 2 worksheet 3

column A column A column B

Civil Isometrics Turbines
Mechanical Vendors Electrical
Electrical Scope Waste Managemet
Instrument Electrical QMS
......ans so on

I want to find the word "Electrical" from my 3worksheets and the results
will appear next to the cell of worksheet 1 column A which is column B and C,
like this

w o r k s h e e t 1
column A column B column C

Civil
Mechanical
Electrical Electrical Electrical
Instrument

how would write my formula?

thanks for any help, I appreciate