Creating a Quick Search Function for Invoice Rates Based on Job Numbers
As an Excel user, have you ever found yourself needing to quickly search for invoice rates based on job numbers? If so, you’re in luck! Today, we’ll be exploring how to create a quick search function that will allow you to select between day and night rates, and display the corresponding price from a database based on the job number inserted in another cell.
The Problem We’re Trying to Solve
Let’s say you have a list of prices for different job numbers in a separate sheet, as shown below:
| Job No | Day Rate | Night Rate |
| — | — | — |
| 106030 | $100 | $150 |
| 107040 | $120 | $180 |
| 108050 | $140 | $200 |
Now, let’s say you want to quickly search for the price of job number 106030 using a search function. You could manually look up the price in the list, but this can be time-consuming and prone to errors. That’s where our quick search function comes in!
The Solution: Creating a Quick Search Function
To create our quick search function, we’ll be using Excel’s built-in SEARCH and INDIRECT functions. Here’s the step-by-step process:
1. Create a column next to your list of prices with the headings “Day Rate” and “Night Rate”.
2. In the first cell of the new column, enter the following formula: =SEARCH(A2,B:C)
In this formula, A2 refers to the cell containing the job number you want to search for (e.g., 106030). B:C refers to the range of cells containing the day and night rates (e.g., B2:C2).
3. Copy the formula down to the rest of the cells in the new column.
4. To display the corresponding price based on the job number, we’ll use the INDIRECT function. In the next cell, enter the following formula: =INDIRECT(B2&”+”&A2)
In this formula, B2&”+”&A2 refers to the job number (e.g., 106030) followed by the “+” sign and the day rate (e.g., $100). The INDIRECT function will return the value in the cell corresponding to the job number and day rate.
5. Copy the formula down to the rest of the cells in the new column.
The Result: A Quick Search Function for Invoice Rates Based on Job Numbers
With these formulas in place, you can now quickly search for invoice rates based on job numbers! Simply select the “Day Rate” or “Night Rate” option, insert the job number, and the corresponding price will be displayed.
Here’s what the final result should look like:
| Job No | Day Rate | Night Rate | Price |
| — | — | — | — |
| 106030 | $100 | $150 | $100 |
| 107040 | $120 | $180 | $120 |
| 108050 | $140 | $200 | $140 |
Conclusion
In this blog post, we’ve explored how to create a quick search function for invoice rates based on job numbers using Excel’s SEARCH and INDIRECT functions. With this solution, you can easily and quickly search for invoice rates without having to manually look up the prices in a list. This can save you time and reduce errors, making your work more efficient and accurate.