Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

IF/AND/OR with multiple conditions

0

Hi,

I'm working in a scaffolding company and we have packing lists for different types of scaffold or related products that goes from the office to the warehouse.

So I've been working on a sheet for rolling scaffold recently. I have encountered an issue where I need multiple conditions for pricing the job. I will try to explain. We have 2 types of scaffold, each can be built in the same height, but there are some different parts for each type, this I have managed to divide so that when i chose type 1 "Smal" (slim) and type 2 "Bred" (wide). I use a dropdown on "Input" column D, row 4. The list from E7 to E21 changes when getting information via the FIND.COLUMN. 

Now, the problem is that the customer can chose to have us assemble it, or they can do it themselves. That information is given by adding or removing an "X" in !Input E4. When the X is removed the price (!Input E25) should be a flat 500,- and not find the price from the sheet "RiktigBred" or "Smal". (B19 to P19). 

So I have managed to make the price change when choosing "Bred" or "Smal" in !Input, but when i tick the "X" for assembly or not the price won't change to the flat 500,-.

I have tried to explain as thoroughly as possible. The sheet is in Norwegian, I don't know if that is going to be a problem (see attachment). Hope you understand and can help :D

excel
MS Office Version: Office 16
posted by: LordBrenden (rep: 10) on Wed Dec 1, 2021 at 2:58 am
Answer
Discuss

Answers

0
Selected Answer

Vergad.

Fuirther to your clarification below, I believe that you should use Excel's IFS function (plus logical AND) so the formula in E25 of sheet Input can have three pairs of tests/outcomes. I think it should be:

=IFS(AND(E4="x",D4="Bred"),HLOOKUP($D$5,RiktigBred!$B$1:$P$19,19),AND(E4="x",D4="Smal"),HLOOKUP($D$5,Smal!$B$1:$P$19,19),E5="",500)
where the middle section in bold is one pair, comprising a test (is it E4=x and size Smal?) and the outcome if the test is met (use  HLOOKUP on row 19 of sheet Smal). Note that I've added some imaginary values in Smal (roughly 60% of the Bred prices) to test that- please see attached revised workbook (and correct the Smal cells shaded yellow).

Please see Don's tutorial IFS Function - Multiple IF Statements in One for further information.

Hope this fixes your issue.

posted by: John_Ru (rep: 6142) on Wed Dec 1, 2021 at 4:40 am
Discuss

Discussion

Yes, but it's only a tidier version of my original formula. The issue is that if i have no "X" in !Input E6 the price in E27 must be according to the prices in the bottom row of "Riktigbred" or "Smal" tabs. These numbers have to be matched with the input "Smal" or "Bred" chosen in the D4 in !Input. So; If i chose "Smal" in D4 and no "X" in E4 the prices have to match the bottom row in the tab "Smal". The same goes for "Bred" and "Riktigbred". Thanks.
LordBrenden (rep: 10) Dec 1, '21 at 6:30 am
Your initial references to E6 and E27 (above) confused me a bit but please see my Revised Answer.
John_Ru (rep: 6142) Dec 1, '21 at 6:56 am
That is exactly what I was looking for. I see now it was pretty simple. Thanks!
LordBrenden (rep: 10) Dec 2, '21 at 1:20 am
I'm glad it helped. Thanks for selecting my answer. 
John_Ru (rep: 6142) Dec 2, '21 at 1:55 am
Vergad. I just realised your Forum profile says "Office 16" but my solution uses the IFS function (which were introduced in Excel 2019. Sorry about that. Do you now have a later version of Excel? (Or need the nested IF equivalent?)

If you are using IFS, you might want to have a default value (if none of the paired tests works- and you get #N/A error). You can do that adding a LAST test pair as follows:
True, "Options not selected"
, changing the bit in bold to suit. It works because there's no real test- you just give IFS a Boolean True.
John_Ru (rep: 6142) Dec 2, '21 at 6:13 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login