Home

Formula Help Please: combining IF AND and IS

sidlola
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

image.png

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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies