• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adjust and modify an equation For Tips

Hany ali

Active Member
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
Code:
=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
 

Attachments

  • Transfer.xlsx
    47 KB · Views: 6
Please inform us what are the requirements to obtain the result in Column G as the expected result in Column L

Regards
 
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 TypeCompany NameVehicle TypeRegion
 

Attachments

  • Untitled.png
    Untitled.png
    105.6 KB · Views: 4
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),"")

82750
 

Attachments

  • Transfer (BY).xlsx
    48.6 KB · Views: 5
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.
 
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.
 
Last edited:
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
 
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
 
Back
Top