SOLVED

More then 64 levels of nesting need a simpler formula with some tweaks

Copper Contributor

Hello all.

I have a need to automate some tasks using Excel. Usually, i don't use any formulas but I decided to give it a try, and just bumped into, of course, the max nesting problem :)

Im on win7, using Excel 2016

I used to have 108 nesting IFs, so I did some research and already made a Vlookup table.

Now my problem is this:

this is my Table1 with some data

 0-33-66-99-12

12-

15

15-1818-2121-2424-27
AAA1AA2AA3AA4AA5AA6AA7AA8AA9
BBB1BB2BB3BB4BB5BB6BB7BB8BB9
CCC1CC2CC3CC4CC5CC6CC7CC8CC9
DDD1DD2DD3DD4DD5DD6DD7DD8DD9
EEE1EE2EE3EE4EE5EE6EE7EE8EE9
FFF1FF2FF3FF4FF5FF6FF7FF8FF9
GGG1GG2GG3GG4GG5GG6GG7GG8GG9
HHH1HH2HH3HH4HH5HH6HH7HH8HH9
III1II2II3II4II5II6II7II8II9
JJJ1JJ2JJ3JJ4JJ5JJ6JJ7JJ8JJ9
KKK1KK2KK3KK4KK5KK6KK7KK8KK9
LLL1LL2LL3LL4LL5LL6LL7LL8LL9

In the First Column are some text strings I have to match (A-L)

The First Row contains some number ranges I have to match. 

Other rows and columns are some unique fixed data strings.

What i actually need is this:

2.2AAA1
5.8CCC2
13.3JJJ5
21KKK8

I need to auto-populate the third column of Table-2 with data from Table-1. The problem I can not solve is that the data from 1st column of Table-2 are not integers and I have to query columns 2-10 from Table-1 to find matching row-column data.

I tried the VLOOKUP approach and XLOOKUP approach but i can't manage to do that. I also started to look CHOOSE formula but I guess it has to do with formula within a formula solution and that's a little too much for me :)

I would appreciate any help. Thank you in advance.

 

Also, I'm posting my original nested IF function that did a good job until it reached nest 64:

Basically, it has 9 occurrences of text string A, 9xB, and so on and ends up in 9x12=108.

I used IF function then used AND to query the A1 column number and then, when a query is matched fetch that unique string and auto-populate the table with it.  

=IF(AND(A1>=0,A1<3.3333,AT1="A"),"1 1|1|1 KE|",IF(AND(A1>=3.3333,A1<6.667,AT1="A"),"1 2|2|2 KE|",IF(AND(A1>=6.667,A1<10,AT1="A"),"1 3|3|3 KE|",IF(AND(A1>=10,A1<13.333,AT1="A"),"1 4|4|4 KE|",IF(AND(A1>=13.333,A1<16.667,AT1="A"),"1 5|5|1 VE|",IF(AND(A1>=16.667,A1<20,AT1="A"),"1 6|6|2 VE|",IF(AND(A1>=20,A1<23.333,AT1="A"),"1 7|7|3 VE|",IF(AND(A1>=23.333,A1<26.667,AT1="A"),"1 8|8|4 VE|",IF(AND(A1>=26.667,A1<30,AT1="A"),"1 9|9|1 SU|",IF(AND(A1>=0,A1<3.3333,AT1="B"),"2 10|1|2 SU|",IF(AND(A1>=3.3333,A1<6.667,AT1="B"),"2 11|2|3 SU|",IF(AND(A1>=6.667,A1<10,AT1="B"),"2 12|3|4 SU|",IF(AND(A1>=10,A1<13.333,AT1="B"),"2 1|4|1 MO|",IF(AND(A1>=13.333,A1<16.667,AT1="B"),"2 2|5|2 MO|",IF(AND(A1>=16.667,A1<20,AT1="B"),"2 3|6|3 MO|",IF(AND(A1>=20,A1<23.333,AT1="B"),"2 4|7|4 MO|",IF(AND(A1>=23.333,A1<26.667,AT1="B"),"2 5|8|1 MA|",IF(AND(A1>=26.667,A1<30,AT1="B"),"2 6|9|2 MA|",IF(AND(A1>=0,A1<3.3333,AT1="C"),"3 7|1|3 MA|",IF(AND(A1>=3.3333,A1<6.667,AT1="C"),"3 8|2|4 MA|",IF(AND(A1>=6.667,A1<10,AT1="C"),"3 9|3|1 RA|",IF(AND(A1>=10,A1<13.333,AT1="C"),"3 10|4|2 RA|",IF(AND(A1>=13.333,A1<16.667,AT1="C"),"3 11|5|3 RA|",IF(AND(A1>=16.667,A1<20,AT1="C"),"3 12|6|4 RA|",IF(AND(A1>=20,A1<23.333,AT1="C"),"3 1|7|1 JU|",IF(AND(A1>=23.333,A1<26.667,AT1="C"),"3 2|8|2 JU|",IF(AND(A1>=26.667,A1<30,AT1="C"),"3 3|9|3 JU|",IF(AND(A1>=0,A1<3.3333,AT1="D"),"4 4|1|4 JU|",IF(AND(A1>=3.3333,A1<6.667,AT1="D"),"4 5|2|1 SA|",IF(AND(A1>=6.667,A1<10,AT1="D"),"4 6|3|2 SA|",IF(AND(A1>=10,A1<13.333,AT1="D"),"4 7|4|3 SA|",IF(AND(A1>=13.333,A1<16.667,AT1="D"),"4 8|5|4 SA|",IF(AND(A1>=16.667,A1<20,AT1="D"),"4 9|6|1 ME|",IF(AND(A1>=20,A1<23.333,AT1="D"),"4 10|7|2 ME|",IF(AND(A1>=23.333,A1<26.667,AT1="D"),"4 11|8|3 ME|",IF(AND(A1>=26.667,A1<30,AT1="D"),"4 12|9|4 ME|",IF(AND(A1>=0,A1<3.3333,AT1="E"),"5 1|1|1 KE|",IF(AND(A1>=3.3333,A1<6.667,AT1="E"),"5 2|2|2 KE|",IF(AND(A1>=6.667,A1<10,AT1="E"),"5 3|3|3 KE|",IF(AND(A1>=10,A1<13.333,AT1="E"),"5 4|4|4 KE|",IF(AND(A1>=13.333,A1<16.667,AT1="E"),"5 5|5|1 VE|",IF(AND(A1>=16.667,A1<20,AT1="E"),"5 6|6|2 VE|",IF(AND(A1>=20,A1<23.333,AT1="E"),"5 7|7|3 VE|",IF(AND(A1>=23.333,A1<26.667,AT1="E"),"5 8|8|4 VE|",IF(AND(A1>=26.667,A1<30,AT1="E"),"5 9|9|1 SU|",,IF(AND(A1>=0,A1<3.3333,AT1="F"),"6 10|1|2 SU|",IF(AND(A1>=3.3333,A1<6.667,AT1="F"),"6 11|2|3 SU|",IF(AND(A1>=6.667,A1<10,AT1="F"),"6 12|3|4 SU|",IF(AND(A1>=10,A1<13.333,AT1="F"),"6 1|4|1 MO|",IF(AND(A1>=13.333,A1<16.667,AT1="F"),"6 2|5|2 MO|",IF(AND(A1>=16.667,A1<20,AT1="F"),"6 3|6|3 MO|",IF(AND(A1>=20,A1<23.333,AT1="F"),"6 4|7|4 MO|",IF(AND(A1>=23.333,A1<26.667,AT1="F"),"6 5|8|1 MA|",IF(AND(A1>=26.667,A1<30,AT1="F"),"6 6|9|2 MA|",IF(AND(A1>=0,A1<3.3333,AT1="G"),"7 7|1|3 MA|",IF(AND(A1>=3.3333,A1<6.667,AT1="G"),"7 8|2|4 MA|",IF(AND(A1>=6.667,A1<10,AT1="G"),"7 9|3|1 RA|",IF(AND(A1>=10,A1<13.333,AT1="G"),"7 10|4|2 RA|",IF(AND(A1>=13.333,A1<16.667,AT1="G"),"7 11|5|3 RA|",IF(AND(A1>=16.667,A1<20,AT1="G"),"7 12|6|4 RA|",IF(AND(A1>=20,A1<23.333,AT1="G"),"7 1|7|1 JU|",IF(AND(A1>=23.333,A1<26.667,AT1="G"),"7 2|8|2 JU|",IF(AND(A1>=26.667,A1<30,AT1="G"),"7 3|9|3 JU|",IF(AND(A1>=0,A1<3.3333,AT1="H"),"8 4|1|4 JU|",IF(AND(A1>=3.3333,A1<6.667,AT1="H"),"8 5|2|1 SA|",IF(AND(A1>=6.667,A1<10,AT1="H"),"8 6|3|2 SA|",IF(AND(A1>=10,A1<13.333,AT1="H"),"8 7|4|3 SA|",IF(AND(A1>=13.333,A1<16.667,AT1="H"),"8 8|5|4 SA|",IF(AND(A1>=16.667,A1<20,AT1="H"),"8 9|6|1 ME|",IF(AND(A1>=20,A1<23.333,AT1="H"),"8 10|7|2 ME|",IF(AND(A1>=23.333,A1<26.667,AT1="H"),"8 11|8|3 ME|",IF(AND(A1>=26.667,A1<30,AT1="H"),"8 12|9|4 ME|",IF(AND(A1>=0,A1<3.3333,AT1="I"),"9 1|1|1 KE|",IF(AND(A1>=3.3333,A1<6.667,AT1="I"),"9 2|2|2 KE|",IF(AND(A1>=6.667,A1<10,AT1="I"),"9 3|3|3 KE|",IF(AND(A1>=10,A1<13.333,AT1="I"),"9 4|4|4 KE|",IF(AND(A1>=13.333,A1<16.667,AT1="I"),"9 5|5|1 VE|",IF(AND(A1>=16.667,A1<20,AT1="I"),"9 6|6|2 VE|",IF(AND(A1>=20,A1<23.333,AT1="I"),"9 7|7|3 VE|",IF(AND(A1>=23.333,A1<26.667,AT1="I"),"9 8|8|4 VE|",IF(AND(A1>=26.667,A1<30,AT1="I"),"9 9|9|1 SU|",IF(AND(A1>=0,A1<3.3333,AT1="J"),"10 10|1|2 SU|",IF(AND(A1>=3.3333,A1<6.667,AT1="J"),"10 11|2|3 SU|",IF(AND(A1>=6.667,A1<10,AT1="J"),"10 12|3|4 SU|",IF(AND(A1>=10,A1<13.333,AT1="J"),"10 1|4|1 MO|",IF(AND(A1>=13.333,A1<16.667,AT1="J"),"10 2|5|2 MO|",IF(AND(A1>=16.667,A1<20,AT1="J"),"10 3|6|3 MO|",IF(AND(A1>=20,A1<23.333,AT1="J"),"10 4|7|4 MO|",IF(AND(A1>=23.333,A1<26.667,AT1="J"),"10 5|8|1 MA|",IF(AND(A1>=26.667,A1<30,AT1="J"),"10 6|9|2 MA|",IF(AND(A1>=0,A1<3.3333,AT1="K"),"11 7|1|3 MA|",IF(AND(A1>=3.3333,A1<6.667,AT1="K"),"11 8|2|4 MA|",IF(AND(A1>=6.667,A1<10,AT1="K"),"11 9|3|1 RA|",IF(AND(A1>=10,A1<13.333,AT1="K"),"11 10|4|2 RA|",IF(AND(A1>=13.333,A1<16.667,AT1="K"),"11 11|5|3 RA|",IF(AND(A1>=16.667,A1<20,AT1="K"),"11 12|6|4 RA|",IF(AND(A1>=20,A1<23.333,AT1="K"),"11 1|7|1 JU|",IF(AND(A1>=23.333,A1<26.667,AT1="K"),"11 2|8|2 JU|",IF(AND(A1>=26.667,A1<30,AT1="K"),"11 3|9|3 JU|",IF(AND(A1>=0,A1<3.3333,AT1="L"),"12 4|1|4 JU|",IF(AND(A1>=3.3333,A1<6.667,AT1="L"),"12 5|2|1 SA|",IF(AND(A1>=6.667,A1<10,AT1="L"),"12 6|3|2 SA|",IF(AND(A1>=10,A1<13.333,AT1="L"),"12 7|4|3 SA|",IF(AND(A1>=13.333,A1<16.667,AT1="L"),"12 8|5|4 SA|",IF(AND(A1>=16.667,A1<20,AT1="L"),"12 9|6|1 ME|",IF(AND(A1>=20,A1<23.333,AT1="L"),"12 10|7|2 ME|",IF(AND(A1>=23.333,A1<26.667,AT1="L"),"12 11|8|3 ME|",IF(AND(A1>=26.667,A1<30,AT1="L"),"12 12|9|4 ME|"
))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

Thanks again

here is attached file

Book1.xlsx 

2 Replies
best response confirmed by macathlon (Copper Contributor)
Solution

@macathlon See attached.

 

Thank you very much @Riny_van_Eekelen
It works like a charm. :)
Yes i was about to start looking at the INDEX & MATCH function. Thanks again! :)
1 best response

Accepted Solutions
best response confirmed by macathlon (Copper Contributor)