Reporting Templates: VLOOKUP to return multiple items and create your mini search engine in Excel

How to Create Your Mini Search Engine in MS Excel

The Reporting Templates Tool is a mini search engine of a database of accounts and their report type in Excel. When an account is entered:

  • all its profile details are displayed using the VLOOKUP function (looks up the database and returns a single value), and
  • a list of what templates are required to generate that account’s report are enumerated (looks up the database and returns multiple values).

Source Data

In this tool, we have a source tab (General) to keep all information about each account – could be what region that account is from, how is it delivered to clients, what reporting period does it follow, and all other specifications required of each account.

In another source tabs (Temps), we enumerate all the templates required to be downloaded and saved of each account before generating its report.

All tabs we utilize the Excel table feature so updating the list in the future won’t hurt our formula. If you got confused what I’m talking about tables, you might wanna go here first.

Output

Then we have our Main tab, which serves as our dashboard of the “search engine.”

We input on cell B2 the account we would want to look up to, then the dashboard displays everything about that account selected.

The upper portion mainly uses the VLOOKUP function. Each item looks up the account from General tab and returns what is needed. That’s pretty easy, right?

The lower portion uses the INDEX-SMALL combo, which should be an array formula. The INDEX-SMALL combo sort of does the VLOOKUP function, but instead of returning a single value, it returns or enumerates multiple values on a range.

create mini search engine excel

Download the sample file: [SAMPLE] Reporting Templates Tool.xlsx

Or! Wanna save yourself from all the work and build yours by just plugging in your data? Just message me below to get the unlocked version!

>> Lae

Lae

Hi! I am Lae from the Philippines. A CPA and frustrated blogger who writes mainly to create a portfolio for her accounting freelancing. I'm an Excel enthusiast in that VBA, complex formula, and Pivots fascinate me. I'm also down for cool PowerPoint presentations and visual digital documents. On the side I'd be putting in some travel experiences here in LDR as my repository of cities, culture, and people.

Leave a Reply

Your email address will not be published. Required fields are marked *