Forum Discussion
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
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)
Exactly that. The headers are Category, Service and Price
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
