Adjust and modify an equation For Tips

[Hany ali]

Hello my Dears ,I want Your help to Modify the Function For Tips Value From Column G in Total Sheet .because It's Not Work

=IFERROR(1*INDIRECT(ADDRESS(INDEX(MATCH($B2&$C2,Transfer!$A$1:$A$617&Transfer!$BB$1:$BB$617,0),),MATCH($E2,Transfer!$B$1:$BA$1,0)+MATCH($D2,OFFSET(Transfer!$B$2,0,0,1,MATCH($E2,Transfer!$B$1:$BA$1,0)+9),0)+1,,,"Transfer")),"")

thanks Alot

 

[bosco_yip]

Please inform us what are the requirements to obtain the result in Column G as the expected result in Column L

Regards

 

[Hany ali]

Thank you for your kind response,It is required to find it in the column G TO get drivers commission amounts on a page Transfer by Multiple Condition as Following

Transfer Type

Company Name

Vehicle Type

Region

 

[bosco_yip]

Try,

In G2, formula copied down :

=IFERROR(OFFSET(Transfer!$A$63,MATCH(F2,N(OFFSET(Transfer!$A$1,ROW($63:$121),MATCH(D2,Transfer!$A$2:$I$2,0)-1+MATCH(E2,Transfer!$B$1:$BA$1,0)-1)),0),MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-1),"")

 

[bosco_yip]

Thanks Alot ..Excellent job, which is really needed ,but WhyYou Start Function From Transfer!$A$63 not From Transfer!$A$1
because in my main File Not Work when i make it from Transfer!$A$63 !!!!

Because your file in the Transfer sheet hidden the row of 4 to 63.

 

[bosco_yip]

Or you can start it from row 3, the formula become:

=IFERROR(OFFSET(Transfer!$A$3,MATCH(F2,N(OFFSET(Transfer!$A$1,ROW($3:$121),MATCH(D2,Transfer!$A$2:$I$2,0)-1+MATCH(E2,Transfer!$B$1:$BA$1,0)-1)),0),MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-1),"")

or this shorter,

=IFERROR(OFFSET(Transfer!$A$3,MATCH(F2,OFFSET(Transfer!$A$3,1,MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-2,119),0),MATCH(D2,Transfer!$A$2:$I$2,0)+MATCH(E2,Transfer!$B$1:$BA$1,0)-1),"")

All result return same.

 

[Hany ali]

Thanks Alot ..Excellent job, which is really needed

 

[bosco_yip]

Thanks Alot ..Excellent job, which is really needed

Please compare with the formula used of mine and yours:

Yours: IFERROR, INDIRECT, ADDRESS, INDEX, MATCH and OFFSET (6 functions)

Mine: IFERROR, OFFSET and MATCH (3 functions)

So,

Simple Offset and Match is enough to solve your quite complex question.

Let us more Excelling by this new year.

Regards
Bosco

 

[Hany ali]

Certainly, of course, this is easier and simpler. I thank you very much with all my heart. I really benefited a lot from your solutions to any problem