• 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.

Copy Formula to Right

tiong999

Member
Hello All,

I found myself frustrated with something lack with my excell skill...and hopefully i have someone i could ask here.

By simply put, i Have 2 sheet :
Master Table
Result

in The Master Table, there will be i input price, and like to have result as i formulated and u can easily see what result i want.
But, formula i put to next column now in Result sheet is manually...and that will put me so much effort and risky of human error.

Please spare ur time a while...i believe this should be easy for excel master here :)

Regards,
TIONG
 

Attachments

  • CHANDOO ASK.xlsx
    26.4 KB · Views: 6
Try........

In J5, formula copy across yo O5 and all copy down :

=IFERROR(TEXT(1/(1/INDEX('MASTER TABLE'!$U$4:$AE$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0))),"0.000"),IFERROR(1/(1/INDEX('MASTER TABLE'!$E$4:$O$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$E$3:$O$3,0)))*INDEX('MASTER TABLE'!$U$1:$AE$1,MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0)),""))

Regards
Bosco
 

Attachments

  • INDEX and MATCH(1).xlsx
    25.5 KB · Views: 4
Try........

In J5, formula copy across yo O5 and all copy down :

=IFERROR(TEXT(1/(1/INDEX('MASTER TABLE'!$U$4:$AE$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0))),"0.000"),IFERROR(1/(1/INDEX('MASTER TABLE'!$E$4:$O$100,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$100=$C4)*('MASTER TABLE'!$D$4:$D$100=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$E$3:$O$3,0)))*INDEX('MASTER TABLE'!$U$1:$AE$1,MATCH(E$3,'MASTER TABLE'!$U$3:$AE$3,0)),""))

Regards
Bosco

Hello Bosco....i'm glad for your assistance again.

I made a slight adjustment to meet what i need...it worked for a case, but how to formulate if have so many case that i want it to be automated?

Please review my adjustment on attach file.

Regards,
TIONG
 

Attachments

  • INDEX and MATCH(2).xlsx
    20.7 KB · Views: 5
Hi Tiong,

1] Here's is a long and complicated formula.

In E4 formula copy across to AA4 and all copy down :

=IFERROR(TEXT(1/(1/INDEX(('MASTER TABLE'!$T$4:$AE$98,'MASTER TABLE'!$AF$4:$AQ$98),MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$98=$C4)*('MASTER TABLE'!$D$4:$D$98=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$T$3:$AE$3,0),MATCH(LOOKUP(2,1/($E$2:E$2<>""),$E$2:E$2),{"SHOP A","SHOP B"},0))),"0.000"),IFERROR(1/(1/INDEX('MASTER TABLE'!$E$4:$P$98,MATCH(1,INDEX(('MASTER TABLE'!$C$4:$C$98=$C4)*('MASTER TABLE'!$D$4:$D$98=$D4),0),0),MATCH(E$3,'MASTER TABLE'!$E$3:$P$3,0)))*INDEX('MASTER TABLE'!$T$1:$AQ$1,MATCH(1,INDEX(('MASTER TABLE'!$T$3:$AQ$3=E$3)*(LOOKUP(COLUMN('MASTER TABLE'!$T$2:$AQ$2),COLUMN('MASTER TABLE'!$T$2:$AQ$2)/('MASTER TABLE'!$T$2:$AQ$2>0),'MASTER TABLE'!$T$2:$AQ$2)=LOOKUP(2,1/($E$2:E$2<>""),$E$2:E$2)),0),0)),""))

2] Due to your merge cell and separates table design, which increase the unnecessary difficulty for the formula design in the Lookup processing.

3] Although the formula can work, it is not recommended, the formula show inefficient and hard to maintain.

4] Try to Google in searching from web : "How to Create a Database from an Excel Spreadsheet".

Regards
Bosco
 

Attachments

  • INDEX and MATCH(3).xlsx
    22 KB · Views: 4
Back
Top