Create your own Excel Search Pt. 1

Introduction

This tutorial will guide you through creating a simple search engine in Excel. By using various Excel functions, you can generate a searchable list that updates based on user input, effectively implementing a fuzzy search feature. This is particularly useful for quickly retrieving data from large datasets.

Step 1: Set Up Your Search Function

1. Create a Search Box:

   • Merge two cells to create a search box.
   • Insert a magnifying glass image into the merged cells for a visual cue.

2. Use the SEARCH Function:

   • In an adjacent cell, enter the formula:

     =SEARCH("search_term", M10, 1)
     

   • Replace "search_term" with a reference to the search box (e.g., M10).
   • This function will return the position of the first character of the search term in your dataset.

3. Drag the Formula Down:

   • Copy the formula down to cover the range of your data.

Step 2: Determine Relevancy

1. Understand the Output:

   • The SEARCH function returns a number indicating the position of the first character.
   • For example, if you enter "a," it may return 6, indicating "a" is the sixth character in the string.

2. Use IFERROR to Clean Up Errors:

   • To handle errors in the search results, wrap your SEARCH function with IFERROR:

     =IFERROR(SEARCH("search_term", M10, 1), "")
     

   • This will replace any errors with a blank.

Step 3: Rank the Relevancy

1. Implement the RANK Function:

   • In the next column over, use the RANK function:

     =RANK(L10, L$10:L$1000, 1)
     

   • This will rank the relevancy numbers based on their position.

2. Adjust for Ties with COUNTIF:

   • To differentiate between identical ranks, add a COUNTIF function:

     =RANK(L10, L$10:L$1000, 1) + COUNTIF(L$10:L10, L10) - 1
     

   • This will ensure ties are assigned different ranks.

Step 4: Handle Errors in Ranking

1. Use IFERROR for Ranking:
   • To avoid displaying errors for blank or irrelevant search results, apply IFERROR:

     =IFERROR(RANK(...), "")
     

   • This will keep your ranking column clean.

Step 5: Create the Lookup Function

1. Set Up VLOOKUP:

   • Use VLOOKUP to retrieve the actual data based on the ranked values:

     =IFERROR(VLOOKUP(1, A$10:C$1000, 3, FALSE), "")
     

   • Adjust the range and column index to fit your data structure.

2. Drag the Formula Down:

   • Copy the VLOOKUP formula down to display results for all ranks.

Conclusion

You've successfully created a basic search engine in Excel that ranks results based on user input. This system utilizes the SEARCH, RANK, COUNTIF, and VLOOKUP functions to provide a dynamic and interactive searching experience. You can further enhance this by implementing additional features, such as filtering across multiple fields or integrating VBA for larger datasets. Experiment with these techniques and look out for the next video, which will cover switching between columns.