Feb 05 2019 03:44 AM
I have a list of people with a two characteristics (Category of client and type of Service) in columns A and B and a separate table of prices on a different tab.
I want a formula to automatically select the correct price based on the combination of the two characteristics. Otherwise "error"
For example, when price is based on Category only, I use:
=IF(ISNUMBER(SEARCH("Cat 1",A1)),Prices!$B$4,"error")
But now I want to combine two characteristics, I can't figure out how to add an AND.
For example, if the client is Category 1 and wants Service A then the price is found at: Prices!$B$4
I'm sure I'm missing something really obvious. Can anybody help please?
Feb 05 2019 05:21 AM
You may use INDEX/MATCH, concrete formula depends on how is you table with prices structured (e.g. three columns Cat, Service, Price, or Service is in the top row as header, etc)
Feb 05 2019 05:34 AM
Exactly that. The headers are Category, Service and Price
Feb 05 2019 06:18 AM
For data like this
the formula could be
=IFERROR(INDEX($H$2:$H$7,MATCH(1,INDEX(($B2=$F$2:$F$7)*($C2=$G$2:$G$7),0),0)), "no price")
and in attached file