VLOOKUP with Two Client Rates: How to Do It

This guide explains how to use Excel’s VLOOKUP function when you have two different rates for the same client (for example, a standard rate and a discounted rate, or a rate per project type). You’ll learn the core technique, when to use it, and common pitfalls to avoid.

Formula

The basic VLOOKUP formula for looking up a single rate is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: the client name or ID you’re searching for.
• table_array: the range of cells that contains the lookup column and the rate column.
• col_index_num: the column number (from the left of the table_array) that holds the rate you want.
• range_lookup: use FALSE for an exact match, or TRUE (or omit) for an approximate match. For client rates, always use FALSE.

To handle two rates, you have two main options: use two separate VLOOKUPs (one for each rate column), or restructure your data so that a single VLOOKUP can return the correct rate based on a second criterion.

Worked Example

Suppose you have a table of client rates:

Client	Standard Rate	Discounted Rate
Acme	100	80
Beta	120	95
Gamma	90	70

If you want to look up the standard rate for “Acme” and the discounted rate for “Acme”, you would write:

=VLOOKUP("Acme", A2:C4, 2, FALSE) → returns 100

=VLOOKUP("Acme", A2:C4, 3, FALSE) → returns 80

If you have a column that says which rate type to use (e.g., “Standard” or “Discounted”), you can combine VLOOKUP with IF:

=IF(rate_type="Standard", VLOOKUP(client, A2:C4, 2, FALSE), VLOOKUP(client, A2:C4, 3, FALSE))

This checks the rate type and pulls the appropriate column.

When to Use Two VLOOKUPs vs. a Single VLOOKUP

Use two separate VLOOKUPs when you need both rates at the same time (e.g., to compare them or calculate a difference). Use a single VLOOKUP with an IF when you only need one rate at a time, but the rate type varies by row.

If your data is structured with multiple rows per client (one for each rate type), you can’t use a simple VLOOKUP because it will only return the first match. In that case, use INDEX/MATCH with multiple criteria, or use XLOOKUP (if you have Excel 365 or 2021) which can handle multiple criteria with the lookup_array and return_array arguments.

Common Mistakes and How to Avoid Them

• Not using FALSE for exact match – If your client names are not sorted, TRUE can return incorrect results. Always use FALSE for exact client names.
• Column index number is off – Count columns from the left of your table_array, not from column A of the sheet. If your table starts at column B, the first column is 1, not 2.
• Leading/trailing spaces in names – If “Acme” has a space after it, VLOOKUP won’t match. Use TRIM() on the lookup value or clean your data.
• Mixing data types – If client IDs are numbers stored as text, VLOOKUP may fail. Convert both to the same type.
• Forgetting to lock the table array – When copying the formula down, use absolute references (e.g., $A$2:$C$4) so the range doesn’t shift.

Alternatives to VLOOKUP for Two Rates

If VLOOKUP becomes too limited, consider these alternatives:

• INDEX/MATCH: More flexible, can look up to the left, and doesn’t require a specific column order.
• XLOOKUP: Available in Excel 365 and 2021, it simplifies multiple criteria and error handling.
• Power Query: If you’re merging tables with multiple rate columns, Power Query can merge on multiple keys.
• Pivot Tables: For summarizing rates by client and rate type, a pivot table might be simpler.

Frequently Asked Questions

Can VLOOKUP return two different columns at once?

No, a single VLOOKUP returns only one value. To get two rates, you need two VLOOKUP formulas (one for each column) or use an array formula (like SUM with VLOOKUP) if you’re summing them.

What if my client has more than two rates?

You can add more columns and use more VLOOKUPs, or restructure your data into a long format (client, rate type, rate) and use a combination of INDEX and MATCH to find the rate based on both client and rate type.

Why does VLOOKUP return #N/A even though the client exists?

Common reasons: the lookup value doesn’t exactly match (extra spaces, different case, or number stored as text), or the table array doesn’t include the correct column. Check for non-printing characters and ensure the lookup column is the first column in the table_array.

Is there a way to use VLOOKUP with a drop-down for rate type?

Yes, you can create a data validation drop-down for the rate type (e.g., “Standard” or “Discounted”) and then use an IF formula that references the cell with the drop-down to choose the correct VLOOKUP column.

Can I use VLOOKUP with two criteria (client and date)?

VLOOKUP alone can’t handle two criteria. You can use INDEX/MATCH with an array formula, or create a helper column that concatenates the two criteria and use VLOOKUP on that helper column.

Does VLOOKUP work with text and numbers mixed?

Yes, but the lookup value and the first column must be of the same data type. If one is text and the other is a number, VLOOKUP will not match. Convert one to the other using TEXT() or VALUE().

Related

• Generate Extent Reports with Selenium in Java
• Golden Era of Smartphones: What It Really Means
• How to Stereo Pair HomePod Speakers

in rate1 | Standard Rate | $/hr | 100 | 0
in rate2 | Discounted Rate | $/hr | 80 | 0
in hours | Hours Worked | hr | 10 | 0
out total | Total Cost | $ | rate1 * hours | 2
out savings | Savings vs Standard | $ | (rate1 - rate2) * hours | 2