Merging 3 IF statements based on text in another field

[HACZN12]

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

 

[Sephi]

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.

 

[HACZN12]

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

 

[Sephi]

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.

=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

 

[HACZN12]

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.

=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

 

[HACZN12]

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.

=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