Formula Help Please: combining IF AND and IS

New Contributor

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies