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
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
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)
Then again add the IF(Iserror function
Use VLOOKUP to find the cell with the unique identifier (rank)
that you just created
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
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.