Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have 5 or so variables that need to be checked in order to determine what price/unit i should use, there are 6 different price/unit. at the moment i am using really long IF(AND(),"","") statements and was wondering if there is a more efficient function i can use to determine this?
Solved by E. E. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 22/04/2018 - 12:49
yea its more or a one off spreadsheet for data management/booking
Excelchat Expert 22/04/2018 - 12:49
Welcome to got it pro
Excelchat Expert 22/04/2018 - 12:49
I will help you today.
Excelchat Expert 22/04/2018 - 12:50
please tell me details.
User 22/04/2018 - 12:50
ok so is there a more efficient formulas to use that accounts for multiple variables besides using if and AND statements?
User 22/04/2018 - 12:51
i am trying to develop a sheet that determines a rate per night based on a number of client variables
User 22/04/2018 - 12:51
where the rate per night varies depending of the date of the booking
Excelchat Expert 22/04/2018 - 12:52
I need to look at the data.
User 22/04/2018 - 12:52
there are 3 different rates depending on time of year
Excelchat Expert 22/04/2018 - 12:52
Can you do that?
Excelchat Expert 22/04/2018 - 12:53
Please list the variables in the preview window.
User 22/04/2018 - 12:55
does that make sense?
Excelchat Expert 22/04/2018 - 12:55
not really.
Excelchat Expert 22/04/2018 - 12:55
This gives me an idea of where do you need the formula.
Excelchat Expert 22/04/2018 - 12:56
But I need to know the determinants for nightly rates.
User 22/04/2018 - 12:56
so trying to use variable such as: age, member status, season, number of nights/ weekend?, in order to determine the nightly rate
User 22/04/2018 - 12:57
at teh moment i am considering using If statements combined with And statements for the logic part to determine this
User 22/04/2018 - 12:57
but was wondering if there is a more efficient function
Excelchat Expert 22/04/2018 - 12:57
There is.
User 22/04/2018 - 12:57
=]
Excelchat Expert 22/04/2018 - 12:57
We can use index/match
User 22/04/2018 - 12:58
ok how would you use those functions? i havent used them before..
Excelchat Expert 22/04/2018 - 12:59
I'm asking for the determinants to show you how to use it.
Excelchat Expert 22/04/2018 - 01:00
We need to create a table.
Excelchat Expert 22/04/2018 - 01:00
Then using that table, we can create the formula.
User 22/04/2018 - 01:01
does what i just input into the preview help?
Excelchat Expert 22/04/2018 - 01:01
it doesn't.
Excelchat Expert 22/04/2018 - 01:01
I need the complete list.
Excelchat Expert 22/04/2018 - 01:01
Please look at column T.
Excelchat Expert 22/04/2018 - 01:02
Please fill the table with the variables.
User 22/04/2018 - 01:02
ahk, so i don't havent that information yet as it will be a "living sheet" with the data inut as it comes in..?
Excelchat Expert 22/04/2018 - 01:03
Then the only thing I can show you is the general syntax of index/match
User 22/04/2018 - 01:04
ok if you could and then i'll try figuring out how it works.
User 22/04/2018 - 01:04
thankyou
Excelchat Expert 22/04/2018 - 01:05
Give me a few minutes.
Excelchat Expert 22/04/2018 - 01:12
=ArrayFormula(index($D$15:$D$18,match(1,($A$15:$A$18=P3)*($B$15:$B$18=Q3),0)))
Excelchat Expert 22/04/2018 - 01:12
Please see the result now.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.