1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

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

Discussion in 'Ask an Excel Question' started by tiong999, May 17, 2017.

1. ### tiong999New Member

Messages:
28
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

File size:
26.4 KB
Views:
6
2. ### bosco_yipWell-Known Member

Messages:
1,247
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

#### Attached Files:

• ###### INDEX and MATCH(1).xlsx
File size:
25.5 KB
Views:
4
Thomas Kuriakose likes this.
3. ### tiong999New Member

Messages:
28

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?

Regards,
TIONG

File size:
20.7 KB
Views:
5
4. ### bosco_yipWell-Known Member

Messages:
1,247
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

File size:
22 KB
Views:
4
5. ### tiong999New Member

Messages:
28
Well.....Im happy with yesterday formula, no need looking slow formula.

Thanks my friend

Regards,
TIONG