Cross posted at msofficeforums. Please read this
Can someone help me please with this in Excel 2010:
In the example of the attached picture:
Example with yellow cells: numbers in A1 and B1 to be detected on the columns E, F, I, J (cells A1 and B1 are always side by side in this way), then these two numbers to be displayed in the cell M3 plus the maximal of the numbers from the cells: C1, G2 and K4 (neighboring cells). The final text (cell M3) must look exactly like in the picture.
Cross posted at msofficeforums. Please read this
Why you not add a source file, not picture?
If you not bother extra columns, then try to solve the problem in the following way
example:
The columns S, T, U (first cell)
Copy => Paste Special => Paste Link
cell W2
ARRAY formula
The cell X2Code:=IFERROR(INDEX(S$2:S$13;MATCH(0;COUNTIF(W$1:W1;S$2:S$13);0));0)
VLOOKUP formula
cell Y2Code:=VLOOKUP(W2;$S$2:$T$13;2;FALSE)
ARRAY formula
Finally cell M1Code:=MAX(((S$2:S$13=W2)*U$2:U$13))
Put formula
Of course this can be solved in a different way in the column W.Code:=CONCATENATE(W2;";";X2;";";Y2)
The first extra column (instead of S-T-U columns)
Unique from three columns
Other columns as described above
Pay attention! You may need to use a comma instead of a semicolon
Last edited by navic; 2015-11-13 at 08:48 AM.
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
I attached the source file (Example.xlsx)
When I try to use formula for W2 it give me error at this point: "S$13"
Formula for X2 give error at this point: "W2"
Formula M1 give an error at this point: "W2"
Anyway, thanks for trying to help me.
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
Please add a link to your cross-posts. You get free help here, have a little respect for those who are here for FREE on their FREE time to help YOU !
With the help of your exemple, I made this for cell M1:
{=A1&";"&B1&";"&MAX(IF(IFERROR(VLOOKUP(A1,E1:G:G,2,FALSE)=B1,0),MAX(C1,VLOOKUP(A1,E1:G:G,3,FALSE)),0),IF(IFERROR(VLOOKUP(A1,I1:K:K,2,FALSE)=B1,0),MAX(C1,VLOOKUP(A1,I1:K:K,3,FALSE)),0))}
Thank you very much for help.
Bookmarks