Merging 3 IF statements based on text in another field

HACZN12

New Member
Joined
Apr 20, 2017
Messages
14
To Whom it Concerns,


I've got 3 different tables for 3 different firms, I've created 3 IF statements which each select a value if the value is below a certain amount it multiplies it by the lowest value in the table; if not it will multiply it by the highest value.

The issue is each IF statement is relevant to a different table. My spreadsheet has a column for 'Firm name'; I need a formula that will look at the firm name and choose the relevant IF statement for the relevant firm.

So far my formula looks something like this:


=IF((AND(FY9="Firm 1")*(FE9<500000)),(FE9*'Legal Fees'!$E$29),(MASTER!FE9*'Legal Fees'!$E$35)OR(IF((AND(FY9="Firm 2")*(FE9<500000)),(FE9*'Legal Fees'!$E$42),(MASTER!FE9*'Legal Fees'!$E$48)OR(IF((AND(FY9="Firm 3")*(FE9<500000)),(FE9*'Legal Fees'!$E$55),(MASTER!FE9*'Legal Fees'!$E$61))


This doesn't seem to work for 2 reasons:

1) I don't seem to have joined each IF statement correctly using the OR function (not sure where i'm going wrong)

2) currently I have the 'AND' function for the firm name only applying to where the initial value is less than 500000; it should be " IF Firm 1 then IF lower that 500000 pull lowest value from Firm 1 Table, if not pull highest value from firm 1 table, IF Firm 2, IF initial value is less that 500000 pull lowest value from Firm 2 table, if not pull highest value from Firm 2 table, IF firm 3, IF value is less that 500000 pull lowest value from Firm 3 table, if not pull highest value from 'Firm 3' table.

Finding this pretty difficult and it would be great if I could get some help as soon as possible!

Many Thanks

HaczN12
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Have you got an example of the spreadsheet to post? how big are the tables for Firm1,2 and 3 and where abouts are these tables located?

If you can send an example of the spreadsheet I believe I can help.
 
Upvote 0
Have you got an example of the spreadsheet to post? how big are the tables for Firm1,2 and 3 and where abouts are these tables located?

If you can send an example of the spreadsheet I believe I can help.


Hi Sephi, thank you for the prompt response, the table looks like this (this is just a mock table I've created as the information I work with is confidential):


Tab A

[TABLE="width: 379"]
<tbody>[TR]
[TD]Reference value[/TD]
[TD]Field for formula[/TD]
[TD]Solicitor[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]2500[/TD]
[TD]Firm A[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]9000[/TD]
[TD]Firm B[/TD]
[/TR]
[TR]
[TD]350[/TD]
[TD]17500[/TD]
[TD]Firm C[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

TAB B


[TABLE="width: 304"]
<tbody>[TR]
[TD][/TD]
[TD]Firm A[/TD]
[TD]Firm B[/TD]
[TD]Firm C[/TD]
[/TR]
[TR]
[TD]Minimum value[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Maximum value[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
</tbody><colgroup><col><col span="3"></colgroup>[/TABLE]


In this example; the formula would be prevalent in the 'Field for Formula' Column, the formula should multiply the value in the 'Reference Value' by either:

-The 'minimum value' in the TAB 2 table if the 'Reference value' is less than 500, if not it will select the 'maximum value'

-It selects the relevant section of the table in 'TAB 2' based on the appropriate firm stated in the 'solicitor' column in TAB

Let me know if you need more info

Many Thanks!

Hacz
 
Upvote 0
TAB A
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reference Value[/TD]
[TD]Field For Formula[/TD]
[TD]Solicitor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]Firm B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Firm C[/TD]
[/TR]
</tbody>[/TABLE]









TAB B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[TD]Firm B[/TD]
[TD]Firm C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Minimum Value[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Maximum Value[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

Using the above as you have described, try using in cell B2 on "TAB A" the following formula to drag down to B4.
Code:
=IF($A2<500,$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Minimum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)),$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Maximum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)))

This is basically saying if your reference value is less than 500, then it'll do an index match match against your firm on TAB B for the minimum value, and if it isn't it'll do an index match match against the maximum value.

Hope this is what you're looking for and is adaptable to your needs :)
 
Upvote 0
TAB A
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reference Value[/TD]
[TD]Field For Formula[/TD]
[TD]Solicitor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]Firm B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Firm C[/TD]
[/TR]
</tbody>[/TABLE]









TAB B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[TD]Firm B[/TD]
[TD]Firm C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Minimum Value[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Maximum Value[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

Using the above as you have described, try using in cell B2 on "TAB A" the following formula to drag down to B4.
Code:
=IF($A2<500,$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Minimum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)),$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Maximum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)))

This is basically saying if your reference value is less than 500, then it'll do an index match match against your firm on TAB B for the minimum value, and if it isn't it'll do an index match match against the maximum value.

Hope this is what you're looking for and is adaptable to your needs :)


Thanks for this Sephi,

It seems to work for Firm A and C, it didn't however calculate correctly for Firm B, I tried modifying it but i'm not too sure what i'm doing wrong,


I attempted to re-write the formula: =IF($A2<500,$A2*INDEX('Tab B'!$B$2:$D$3,MATCH('Tab B'!$A$2,'Tab B'!$A$2:$A$3,0),MATCH('Tab A'!$C$2,'Tab B'!$B$1:$D$1,0)),'Tab A'!$A2*INDEX('Tab B'!$B$2:$D$3,MATCH('Tab B'!$A$3,'Tab B'!$A$2:$A$3,0),MATCH('Tab A'!$C2,'Tab B'!$B$1:$D$1,0)))




But got the following results:


[TABLE="width: 542"]
<tbody>[TR]
[TD]Reference Value[/TD]
[TD]Field For Formula[/TD]
[TD]Solicitor[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]2500[/TD]
[TD]Firm A[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]3000[/TD]
[TD]Firm B[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]30000[/TD]
[TD]Firm C[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Field B2 displays 3000 when it should calculate 300 x 30 and give 9000, it must be somehow pulling from the wrong minimum!

Do you know what could be done to change this?

Many Thanks
 
Upvote 0
TAB A
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Reference Value[/TD]
[TD]Field For Formula[/TD]
[TD]Solicitor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]250[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]Firm B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]500[/TD]
[TD][/TD]
[TD]Firm C[/TD]
[/TR]
</tbody>[/TABLE]









TAB B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Firm A[/TD]
[TD]Firm B[/TD]
[TD]Firm C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Minimum Value[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Maximum Value[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

Using the above as you have described, try using in cell B2 on "TAB A" the following formula to drag down to B4.
Code:
=IF($A2<500,$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Minimum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)),$A2*INDEX('TAB B '!$B$2:$D$3,MATCH("Maximum Value",'TAB B '!$A$2:$A$3,0),MATCH($C2,'TAB B '!$B$1:$D$1,0)))

This is basically saying if your reference value is less than 500, then it'll do an index match match against your firm on TAB B for the minimum value, and if it isn't it'll do an index match match against the maximum value.

Hope this is what you're looking for and is adaptable to your needs :)

Had a go at modifying the formula and it now works perfect! thanks a lot for helping me with this :D
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top