Can a single cell be concatenated?

[kgoshia]

Below is my single cell formula that is only 1/4 of the way completed, however excel only allows one more argument before it hits its limit of 64 functions.

=IFERROR(IF(AND(E5<=10,E11=1,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=1,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=1,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=10,E11=2,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=10,E11=2,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=10,E11=2,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=10,E11=3,E14=1),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=10,E11=3,E14=2),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=10,E11=3,E14=3),VLOOKUP(1,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=12.5,E11=1,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=1,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=1,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=12.5,E11=2,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=12.5,E11=2,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=12.5,E11=2,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=12.5,E11=3,E14=1),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=12.5,E11=3,E14=2),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=12.5,E11=3,E14=3),VLOOKUP(2,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=16,E11=1,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=1,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=1,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=16,E11=2,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=16,E11=2,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=16,E11=2,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=16,E11=3,E14=1),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=16,E11=3,E14=2),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=16,E11=3,E14=3),VLOOKUP(3,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=20,E11=1,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=1,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=1,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=20,E11=2,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=20,E11=2,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=20,E11=2,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=20,E11=3,E14=1),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=20,E11=3,E14=2),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=20,E11=3,E14=3),VLOOKUP(4,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=25,E11=1,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=1,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=1,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=25,E11=2,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=25,E11=2,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=25,E11=2,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=25,E11=3,E14=1),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=25,E11=3,E14=2),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=25,E11=3,E14=3),VLOOKUP(5,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=32,E11=1,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=1,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=1,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=32,E11=2,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=32,E11=2,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=32,E11=2,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=32,E11=3,E14=1),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=32,E11=3,E14=2),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=32,E11=3,E14=3),VLOOKUP(6,'Minimum Creepage 2N'!A1:I44,9,0),IF(AND(E5<=40,E11=1,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=1,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=1,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,3,0),IF(AND(E5<=40,E11=2,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,4,0),IF(AND(E5<=40,E11=2,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,5,0),IF(AND(E5<=40,E11=2,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,6,0),IF(AND(E5<=40,E11=3,E14=1),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,7,0),IF(AND(E5<=40,E11=3,E14=2),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,8,0),IF(AND(E5<=40,E11=3,E14=3),VLOOKUP(7,'Minimum Creepage 2N'!A1:I44,9,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),)

 

[WarfritLive]

Can you post a list of inputs /outputs? There's definitely a better way to do this.

 

[kgoshia]

This would be my VLOOKUP table I have below. The user has 3 items that to fill in to obtain the correct answer. First the user has to fill in the (V) for working votlage, then a (1,2 or 3) for a degree of protection, then the final argument would be a (I,II or III) for the type of material used to prove along with the other 2 condition to achieve proper protection from being electrically shocked

RMS WORKING VOLTAGE up to and including V	Pollution degree
1 (a)	2	3
Material group
I, II, IIIa, IIIb	I	II	IIIa, IIIb	I	II	IIIa, IIIb (see Note)
10	0,08	0,4	0,4	0,4	1,0	1,0	1,0
12.5	0,09	0,42	0,42	0,42	1,05	1,05	1,05
16	0,1	0,45	0,45	0,45	1,1	1,1	1,1
20	0,11	0,48	0,48	0,48	1,2	1,2	1,2
25	0,125	0,5	0,5	0,5	1,25	1,25	1,25
32	0,14	0,53	0,53	0,53	1,3	1,3	1,3
40	0,16	0,56	0,8	1,1	1,4	1,6	1,8
50	0,18	0,6	0,85	1,2	1,5	1,7	1,9
63	0,2	0,63	0,9	1,25	1,6	1,8	2,0
80	0,22	0,67	0,9	1,3	1,7	1,9	2,1
100	0,25	0,71	1,0	1,4	1,8	2,0	2,1
125	0,28	0,75	1,05	1,5	1,9	2,1	2,4
160	0,32	0,8	1,1	1,6	2,0	2,2	2,5
200	0,42	1,0	1,4	2,0	2,5	2,8	3,2
250	0,56	1,25	1,8	2,5	3,2	3,6	4,0
320	0,75	1,6	2,2	3,2	4,0	4,5	5,0
400	1,0	2,0	2,8	4,0	5,0	5,6	6,3
500	1,3	2,5	3,6	5,0	6,3	7,1	8,0
630	1,8	3,2	4,5	6,3	8,0	9,0	10
800	2,4	4,0	5,6	8,0	10	11	12,5
1000	3,2	5,0	7,1	10	12,5	14	16
1250	4,2	6,3	9,0	12,5	16	18	20
1600	5,6	8,0	11	16	20	22	25
2000	7,5	10	14	20	25	28	32
2500	10	12,5	18	25	32	36	40
3200	12,5	16	22	32	40	45	50
4000	16	20	28	40	50	56	63
5000	20	25	36	50	63	71	80
6300	25	32	45	63	80	90	100
8000	32	40	56	80	100	110	125
10000	40	50	71	100	125	140	160

<colgroup><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

 

[WarfritLive]

I figured it out by staring at your formula. Use this:

=IFERROR(VLOOKUP(MATCH(E5,{10;12.5;16;20;25;32;40}),Mincreep!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Edit: You can add to the MATCH() array as needed without changing anything else.
ie. ...25;32;40;41;42;43;53;66;67;89;90}

 

[SpillerBD]

I don't understand where the desired result is in your table.
I don't see how criteria 2 & 3 can independently vary a result based on your table.

 

[SpillerBD]

I figured it out by staring at your formula. Use this:

=IFERROR(VLOOKUP(MATCH(E5,{10;12.5;16;20;25;32;40}),Mincreep!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),FALSE),"There's an error, yo")

Edit: You can add to the MATCH() array as needed without changing anything else.
ie. ...25;32;40;41;42;43;53;66;67;89;90}

I would make it a table or named range so to require the individual listing of the voltages. That array already exist, why manually duplicate it?

 

[WarfritLive]

SpillerBD, you are correct.
As I said, I created the formula based on studying his, before I had seen his follow up post.
Better formula is:

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!A2:A50),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),F ALSE),"There's an error, yo")

 

[kgoshia]

Below are 2 pictures of the spreadsheet, the first picture shows the 3 question the user has to answer with the red circles around the cell and the cell will the green circle is the equation that I am stuck on.

[/URL][/IMG]

 

[kgoshia]

below in the VLOOKUP table

[/URL][/IMG]

 

[WarfritLive]

The array location is different than my last formula. Try this :

=IFERROR(VLOOKUP(MATCH(E5,'Minimum Creepage 2N'!A6:A26),'Minimum Creepage 2N'!A1:I144,CHOOSE(E11,3,CHOOSE(E14,4,5,6),CHOOSE(E14,7,8,9)),F ALSE),"There's an error, yo")