Formula Help Please: combining IF AND and IS

[sidlola]

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?

[#2]

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)

[#3]

Exactly that. The headers are Category, Service and Price

[#4]

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