Hi,
I am trying to find the shortest and fastest formula to find E2 (without changing the format of course...)
E2 has to go first on sheet x,y and z depending on A and next I have to refer the data depending on 1st, 2nd, and 3rd.
I achieved to do an INDEX(array,Match,Match) formula but I am struggling to understand how I can refer to the two variables (X,y,z and 1st, 2nd, and 3rd).
I hope it's clear and that you can help me.
Thank you
Regards
Last edited by Naf_Fr; 11-11-2020 at 09:46 AM.
Because your tables have identical horizontal and vertical headers and the tables are evenly spaced, you should be able to use
Formula:
Please Login or Register to view this content.
I had to use the IFERROR portion because it looks like you might have (as in the case of row 3, values which are lower than your lowest lookup value (-20%).In the attached file, I put a 7 into each of the cells that I calculated should be returned (they had 0 originally which wouldn't tell me much)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks a lot for your help!
Sorry about the data I gave, it is not the extensive table I have, this is why it results in 0, so thank you for adding the "7".
As you may see on the excel workbook, I have 4 sheets, "sheet 1", "x", "y", and "z", and the data is changing according to that with your formula.
I understand the "INDIRECT(A3&"!C2")" but why the data is changing to the correct sheet with the formula "MATCH(D3,x!$B$2:$B$26,0)" which includes only the "x" sheet?
Thanks!
Last edited by Naf_Fr; 10-22-2020 at 06:21 PM.
OFFSET and INDIRECT are both volatile functions, so if one has to use one, might as well use the other. However, if there'd be a lot of these, they could cause significant recalculation lag.
Also, while OP's worksheet shows same % ranges across columns D to L and in rows 4 to 12, 16 to 24 and 28 to 36 for 1st, 2nd and 3rd in worksheets x, y, and z, and you stated your reliance on that similarity, it may be the OP's sample was oversimplified. Meaning it may be necessary to match against left column and top row separately by ordinal and worksheet. At that point, a formula stemming from yours would be long. However, indexing across worksheets and for different tables within worksheets will necessarily involve LONG formulas.
If keeping formulas short is a priority, it may be necessary to use defined names. In this case, worksheet-level names like T3rd referring to =x!$D$4:$L$12 in worksheet x, similarly for T2nd and T1st in x, and similarly for all 3 tables in y and z. As well as names like T3rdT referring to =x!$C$3:$L$3 and T3rdL referring to =x!$C$4:$C$12 in worksheet x, similarly for T2nd and T1st in x, and similarly for all 3 tables in y and z. Then one could use the still long formula
=INDEX(CHOOSE(3*(MATCH(A3,{"x";"y";"z"})-1)+MATCH(LEFT(D3,1),{1;2;3}),x!T1st,x!T2nd,x!T3rd,y!T1st,y!T2nd,y!T3rd,z!T1st,z!T2nd,z!T3rd),
MATCH(C3,INDEX(CHOOSE(3*(MATCH(A3,{"x";"y";"z"})-1)+MATCH(LEFT(D3,1),{1;2;3}),x!T1stL,x!T2ndL,x!T3rdL,y!T1stL,y!T2ndL,y!T3rdL,z!T1stL,z!T2ndL,z!T3rdL),
MATCH(B3,INDEX(CHOOSE(3*(MATCH(A3,{"x";"y";"z"})-1)+MATCH(LEFT(D3,1),{1;2;3}),x!T1stT,x!T2ndT,x!T3rdT,y!T1stT,y!T2ndT,y!T3rdT,z!T1stT,z!T2ndT,z!T3rdT))
My point: Excel handles this type of layout quite poorly. Far better to use a layout Excel could handle more easily. That means consolidating the 9 tables into a single table. Macros much reduce the pain of building such tables.
I can use "MATCH(D3,x!$B$2:$B$26,0)" because all of your tables axes are identical. If this were not the case, you would need to add more indirects to make sure that the proper axes are being used.
Thank you for your help guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks