Excel 2007 : Search Engine for excel data

Hi all I am newbie to excel
I would like to create a search engine for the data which is present in excel workbook can you please help me...guys..?
though I have read the search function I got no idea whether it would be helpful for searching the whole workbook or not?

I have also attached an excel please help me....


Thanks in advance..

Hi and welcome to the forum.

The function that most suits your needs is VLOOKUP which finds the input in I5 and then it looks down the list in Data1 until it finds what is in I5 and then returns the answer depending on which column you have chosen. Your case is a little more complicated because you want all the related data and the original lookup value and a message if the input is not found. This combines everything you asked for. Paste it into I8 and search with input in I5.

=IF(ISNA(MATCH($I$5,Data1!$E$4:$E$100,0)),"No such code",$I$5&" "&VLOOKUP($I$5,Data1!$E$4:$G$8,2,FALSE)&" "&VLOOKUP($I$5,Data1!$E$4:$G$8,3,FALSE))

I hope I understood correctly.

Regards

Russell

I can't see the advantage of having the data all in one cell and I'm not aware of a way do do this as you request. I considered merging the three cells below and then narrowing the width and then formatting alignment and wrapping text but that would not work with the long text string for reason 93652.

From an Excel point of view it would be much better to have the results in the three cells I8,9 &10 and separate the formula to these.

=IF(ISNA(MATCH($I$5,Data1!$E$4:$E$100,0)),"No such code",$I$5)

=IF(ISNA(MATCH($I$5,Data1!$E$4:$E$100,0)),"  ",VLOOKUP($I$5,Data1!$E$4:$G$8,2,FALSE)

=IF(ISNA(MATCH($I$5,Data1!$E$4:$E$100,0))," ",VLOOKUP($I$5,Data1!$E$4:$G$8,3,FALSE)

Thanks for the rep. I appreciate the sentiment but please don't take offence if I don't accept the friendship request. I'm not yet comfortable with the social network etc scene.

Cheers

Russell