Home > Blockchain >  Google Sheets: Return a single value from array using multiple criteria
Google Sheets: Return a single value from array using multiple criteria

Time:10-04

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

enter image description here

Demo

enter image description here

Used formulas help
enter image description here

or arrayformula of it:

=INDEX(IFNA(VLOOKUP(B1:B&C1:C, 
 SPLIT(FLATTEN(Rates!A2:A&Rates!B1:E1&"×"&Rates!B2:E), "×"), 2, )))
  • Related