Archive from April, 2012
Apr 27, 2012 - management, Technology    No Comments

Google like Search function in Excel

Excel_search_2003

One of the people that I work with came to me the other day and showed me a very extensive database of scholarly articles that he had built in Excel.

Now that his team had finished populating the Database they wanted a Google like search function that would let someone who was not familiar with the contents to easily find what they needed.

Confident in the fact that the logic of Excel would once again come to my rescue I responded in a strong voice, “Of course you can” while ignoring that thing in the back of my mind saying “Knowing it can be done and actually doing it are two very different things”….

But now that I had promised it just had to be done. The other limitation was that it had to be done in Excel 2003. Logically there is no difference….but then reality does not always follow logic I find.

12 hours later and with my hair looking like “Young Einstein” I had done it.

Idea and Explanation

*Note, you have to use IF(Iserror),”",function,) to remove #Value errors or the results will not show properly

————

Simply searching the DB would not do. The results have to be ranked by relevance just like Google does.

But you do start with the search function and apply it to your data set

=IF(ISERROR(SEARCH($F$6,J8,1)),” “,SEARCH($F$6,J8,1))

the $F$6 is the fixed position of the “search box” and this function will find the first instance of whatever it is that you are searching for within the assigned cell (J8)

The second step is then to rank your results in order of relevance. This function does this depending on the number given to the cell by the “search function”

 

Fortunately Excel offers a nifty function called RANK

=IF(ISERROR(RANK(I8,$I$8:$I$66,1)+COUNTIF($I$7:I7,I8)),” “,RANK(I8,$I$8:$I$66,1)+COUNTIF($I$7:I7,I8)) 

This looks unwieldy since it is long but do not fear.

Rank your cell within the cell range (I8,$I8:$I$66)

Give the cell a unique identifier in case there are more

than one result with the same rank (very possible)

+Countif($I$7:I7,I8)

Then again add the IF(Iserror function

Final Step

Use VLOOKUP to find the cell with the unique identifier (rank)

that you just created

=IF(ISERROR(VLOOKUP(A8,$H$8:$J$66,3,FALSE)),”",VLOOKUP(A8,$H$8:$J$66,3,FALSE))

Set you array to include both your data set and the “search” and “ranking” results you just produced. (You can hide them later once finished with the design)

Put a 1-10 next to where you want your results. Have VLOOKUP search for that ranking number within the array and respond with the information in cell 3 (see formula)

and set to FALSE

 

Picture

I am hiding some rows in the sheet but they are unrelated to this function.

If you set up the data like this and change the Row and cell notations to fit your sheet I guarantee this will work.

 

Have Fun

Kris

 

Related topics
Pages:1234»