Chenge the highest number in a cell with a value range from another sheet

[spheedy]

I have a table of 7 columns and 100+ rows. I need to find out the biggest number on each row and for this i have used conditional formatting and this formula =B2=MAX($B2:$H2) and fill the cell with highest number with a color to be able easy identify it.

Now this is where i am stuck. I need all those colored cells with the highest number in them, from each row, to be changed with a certain number that is located in sheet 2.
That specific number is defined by a 10 % difference of a base number (sheet2, row 2), as seen each number is -10% of that base number.

So for each colored cell in sheet 1 , based on the column it is located in (from 1-7), i need to find the corespondent number based on the value range.

Example:

sheet 1: cell G 13 has number 230945, that number is located in column 6 so the value should the range of values in sheet 2 column 6 and is between 192535 and 231041 so it should be changed to 21.

Notice that if that number was present in column 7 the value range for 21 is between 192393 and 230871 , and the number in sheet 1 is 230945 so its bigger then the highest value therefore it will fall into the value bracket of 22 witch is between 230871 and 269350. so that is why it is important that the value range should be looked for in the matching column

And 3rd , after this if it will be possible to sum all this changed cells and give a Total value in another cell, at the end of the table or anywhere else.

[KOKOSEK]

Try like below:

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	320771	326351	331130	259543	266361	273052	279843		320771	326351	24	259543	266361	273052	279843
3	223453	232337	240503	248655	234532	244931	255680		223453	232337	240503	248655	234532	244931	22
4	178352	187956	196507	204519	181964	192417	202674		178352	187956	196507	21	181964	192417	202674
5	270966	278090	284504	272563	281287	289824	298676		270966	278090	284504	272563	281287	289824	23
6	174142	183386	191747	199829	183155	193578	204081		174142	183386	191747	199829	183155	193578	21
7	257272	237714	246265	254277	262861	251433	261690		257272	237714	246265	254277	22	251433	261690
8	181720	190604	198770	206922	185266	195665	206414		181720	190604	198770	21	185266	195665	206414
9	318950	274490	280904	287302	274699	283236	292088		24	274490	280904	287302	274699	283236	292088
10	262416	243725	251891	260043	268767	258707	269456		262416	243725	251891	260043	268767	258707	23

	J	K	L	M
1	after changes
2	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(B2>OFFSET(Sheet2!$A$3,0,IF(B2=MAX($B2:$H2),B$1,B2)-1,11,1),1,0),0)),B2)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(C2>OFFSET(Sheet2!$A$3,0,IF(C2=MAX($B2:$H2),C$1,C2)-1,11,1),1,0),0)),C2)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(D2>OFFSET(Sheet2!$A$3,0,IF(D2=MAX($B2:$H2),D$1,D2)-1,11,1),1,0),0)),D2)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(E2>OFFSET(Sheet2!$A$3,0,IF(E2=MAX($B2:$H2),E$1,E2)-1,11,1),1,0),0)),E2)
3	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(B3>OFFSET(Sheet2!$A$3,0,IF(B3=MAX($B3:$H3),B$1,B3)-1,11,1),1,0),0)),B3)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(C3>OFFSET(Sheet2!$A$3,0,IF(C3=MAX($B3:$H3),C$1,C3)-1,11,1),1,0),0)),C3)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(D3>OFFSET(Sheet2!$A$3,0,IF(D3=MAX($B3:$H3),D$1,D3)-1,11,1),1,0),0)),D3)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(E3>OFFSET(Sheet2!$A$3,0,IF(E3=MAX($B3:$H3),E$1,E3)-1,11,1),1,0),0)),E3)
4	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(B4>OFFSET(Sheet2!$A$3,0,IF(B4=MAX($B4:$H4),B$1,B4)-1,11,1),1,0),0)),B4)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(C4>OFFSET(Sheet2!$A$3,0,IF(C4=MAX($B4:$H4),C$1,C4)-1,11,1),1,0),0)),C4)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(D4>OFFSET(Sheet2!$A$3,0,IF(D4=MAX($B4:$H4),D$1,D4)-1,11,1),1,0),0)),D4)	=IFERROR(INDEX(Sheet2!$H$3:$H$12,MATCH(1,IF(E4>OFFSET(Sheet2!$A$3,0,IF(E4=MAX($B4:$H4),E$1,E4)-1,11,1),1,0),0)),E4)

Formula into J2:

Please Login or Register  to view this content.

accept with Ctrl+Shift+Enter (not just Enter) as is array formula, then drag it right and down.

[spheedy]

Wow....i knew you ppl here are excel guru but this is to much ))

thank you very very much

[KOKOSEK]

Happy to help then. Thanks for rep.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.