I provide different services for my job, but my rates vary from client to client, I'd like a formula to get the rate value (0.04, in this case) using two values, the name of the client "Client 3" and the service in question "Service 2".
I have created a spreadsheet to better illustrate what I am trying to accomplish, hopefully someone can help.
Rates tab.
| Client | Service 1 | Service 2 | Service 3 |
|---|---|---|---|
| Client 1 | 0.06 | 0.02 | 0.08 |
| Client 2 | 0.07 | 0.03 | 0.09 |
| Client 3 | 0.08 | 0.04 | 0.1 |
| Client 4 | 0.09 | 0.05 | 0.11 |
| Client 5 | 0.1 | 0.06 | 0.12 |
| Client 6 | 0.11 | 0.07 | 0.13 |
| Client 7 | 0.12 | 0.08 | 0.14 |
| Client 8 | 0.13 | 0.09 | 0.15 |
| Client 9 | 0.14 | 0.1 | 0.16 |
| Client 10 | 0.15 | 0.11 | 0.17 |
Sheet 1 where results go
| Project name | Client 3 | Service 2 | {formula here}0.04 |
|---|
Demo
Used formulas help
or arrayformula of it:
=INDEX(IFNA(VLOOKUP(B1:B&C1:C,
SPLIT(FLATTEN(Rates!A2:A&Rates!B1:E1&"×"&Rates!B2:E), "×"), 2, )))