Array formula

[Edgar_]

I have this array formula that returns a wire size depending on my voltage drop

=INDEX({14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},
    MATCH(TRUE,
        CHOOSE($C11,
            PRODUCT(4, $AL11, $AC11) / PRODUCT((220 / 3^0.5), VLOOKUP(N(IF(
                {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19},
                {14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000}
                )), tbSecciones, 2, FALSE)),
            PRODUCT(2, $AL11, $AC11) / PRODUCT(220, VLOOKUP(N(IF(
                {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19},
                {14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000}
                )), tbSecciones, 2, FALSE)),
            PRODUCT(2, 3^0.5, $AL11, $AC11) / (220 * VLOOKUP(N(IF(
                {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19},
                {14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000}
                )), tbSecciones, 2, FALSE))
        ) < 3,0))

However, the N function is getting rid of any matches for "1/0","2/0","3/0","4/0" in the tbSecciones table, which means I don't get those wire sizes. Does anyone know workarounds?

Note: I don't really understand the formula very well, so if you can provide me a better approach, I would appreciate it.

Thanks!

EDIT: sorry about the title

 

[StephenCrump]

The problem might be with the N() function, which will convert "1/0", "2/0" etc to 0.

I think your formula can be simplified to:

=LET(n,{14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},V,VLOOKUP(n,tbSecciones,2,),INDEX(n,MATCH(TRUE,$AL11*$AC11/220/V*CHOOSE($C11,4*3^0.5,
2,2*3^0.5)<3,)))

 

[Edgar_]

Thank you for the answer, Stephen.

The problem might be with the N() function, which will convert "1/0", "2/0" etc to 0.

Absolutely, it's turning my strings into zeroes and those strings are lookup values too, so I can't let them go.

I believe it would work with your approach, but I'm sorry because I can't confirm. I do not have that function available.

I appreciate the effort and I'm sorry I did not clarify my limitations from the beginning. I'm using Excel 2016.

 

[StephenCrump]

In that case, will this work for you?

=INDEX({14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},MATCH(TRUE,$AL11*$AC11/220/VLOOKUP({14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},tbSecciones,2,)*CHOOSE($C11,4*3^0.5,
2,2*3^0.5)<3,))

 

[Edgar_]

In that case, will this work for you?

=INDEX({14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},MATCH(TRUE,$AL11*$AC11/220/VLOOKUP({14,12,10,8,6,4,2,"1/0","2/0","3/0","4/0",250,300,350,400,500,600,750,1000},tbSecciones,2,)*CHOOSE($C11,4*3^0.5,
2,2*3^0.5)<3,))

Unfortunately, it returns #N/A because it fails at MATCH(TRUE,TRUE,) but I realize I should be posting a dataset. I'll take care of that and I'll get back.

Thank you very much Stephen.

 

[Edgar_]

This thread has been solved. I had to make several changes and I did it all little by little until I understood what was happening.

1. If you notice in my original formula, there is a table reference. That table did not match the expected values to return, so I fixed that.
2. Instead of having CHOOSE embedded in MATCH, I moved it to the top of the hierarchy, before INDEX for more simplicity.
3. I carefully modified the amount of parentheses to make absolutely sure that the order of operations was being done correctly.
4. I toyed with the arrays and their operations, I figured out {1,2}*{3,4} = {3,8}, so the positions are multiplied. Not a groundbreaking discovery, but those basics helped me understand that my arrays had to be the same size and it helped me figure out how to arrange my booleans.
5. Because of this understanding, I was able to add the second condition to the operation. I was initially going to use AND, but multiplying the arrays was easier to understand while building the formula.

And the final result looks something like this:

=CHOOSE([Phase],
INDEX(tbSections[WireSize],MATCH(TRUE,(((4*[Length]*[INOM])/(([VOLTAGE]/3^0.5)*tbSections[Section]))<3)*INDIRECT("tbAmpacities["&[Material]&"]")>[ICORR],0)),
INDEX(tbSections[WireSize],MATCH(TRUE,(((2*[Length]*[INOM])/([VOLTAGE]*tbSections[Section]))<3)*INDIRECT("tbAmpacities["&[Material]&"]")>[ICORR],0)),
INDEX(tbSections[WireSize],MATCH(TRUE,(((2*(3^0.5)*[Length]*[INOM])/([VOLTAGE]*tbSections[Section]))<3)*INDIRECT("tbAmpacities["&[Material]&"]")>[ICORR],0)))

Thank you very much, Stephen, your examples helped me realize the importance of the operations and the reliability of INDEX/MATCH over VLOOKUP for this type of operation.

 

[StephenCrump]

Well done! It sounds like a good learning experience for you.