New Contributor

# Formula Help Please: combining IF AND and IS

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?

3 Replies

# Re: Formula Help Please: combining IF AND and IS

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)

# Re: Formula Help Please: combining IF AND and IS

Exactly that. The headers are Category, Service and Price

# Re: Formula Help Please: combining IF AND and IS

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