Hey everyone, I’m struggling with a spreadsheet problem. I offer various services to clients, but my rates differ for each one. I need a formula to pull out the correct rate based on two inputs: the client name and the service type.
Here’s what my data looks like:
| Client |
Service A |
Service B |
Service C |
| ABC Co |
0.05 |
0.03 |
0.07 |
| XYZ Inc |
0.06 |
0.04 |
0.08 |
| 123 Ltd |
0.07 |
0.05 |
0.09 |
For example, if I input ‘XYZ Inc’ and ‘Service B’, I want the formula to return 0.04.
Can anyone help me figure out how to do this? I’ve tried a few things but can’t seem to get it right. Thanks in advance for any suggestions!
Have you considered using the VLOOKUP function with a helper column? It’s a straightforward method that works well for this type of problem. First, create a new column that combines the client name and service type. Then use VLOOKUP to find the correct rate. For example, if your helper column starts in E2, your formula might look like this:
=VLOOKUP(A8&B8, E2:F4, 2, FALSE)
where A8 is the client name and B8 is the service type. This approach is easy to maintain and update as your client list or services change. It’s also less prone to errors compared to more complex nested formulas.
Drawing from my own experience with Excel formulas in similar scenarios, I found the INDEX-MATCH method to be particularly reliable. You can use a combination of two MATCH functions inside an INDEX formula to locate both the client row and the service column. For example:
=INDEX($B$2:$D$4, MATCH(A8, $A$2:$A$4, 0), MATCH(B8, $B$1:$D$1, 0))
Here, A8 is the cell where you enter the client name, and B8 is where you input the service. This method dynamically finds the correct intersection in your pricing matrix. It’s a practical approach if you need to adapt to changes in your dataset over time.
hey claire, have u tried using index and match together? it’s pretty nifty for this kinda thing. here’s a formula that might work: =INDEX($B$2:$D$4, MATCH(A8, $A$2:$A$4, 0), MATCH(B8, $B$1:$D$1, 0)). just put ur client in A8 and service in B8. hope this helps!