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-3 | 3-6 | 6-9 | 9-12 | 12- 15 | 15-18 | 18-21 | 21-24 | 24-27 |
| A | AA1 | AA2 | AA3 | AA4 | AA5 | AA6 | AA7 | AA8 | AA9 |
| B | BB1 | BB2 | BB3 | BB4 | BB5 | BB6 | BB7 | BB8 | BB9 |
| C | CC1 | CC2 | CC3 | CC4 | CC5 | CC6 | CC7 | CC8 | CC9 |
| D | DD1 | DD2 | DD3 | DD4 | DD5 | DD6 | DD7 | DD8 | DD9 |
| E | EE1 | EE2 | EE3 | EE4 | EE5 | EE6 | EE7 | EE8 | EE9 |
| F | FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9 |
| G | GG1 | GG2 | GG3 | GG4 | GG5 | GG6 | GG7 | GG8 | GG9 |
| H | HH1 | HH2 | HH3 | HH4 | HH5 | HH6 | HH7 | HH8 | HH9 |
| I | II1 | II2 | II3 | II4 | II5 | II6 | II7 | II8 | II9 |
| J | JJ1 | JJ2 | JJ3 | JJ4 | JJ5 | JJ6 | JJ7 | JJ8 | JJ9 |
| K | KK1 | KK2 | KK3 | KK4 | KK5 | KK6 | KK7 | KK8 | KK9 |
| L | LL1 | LL2 | LL3 | LL4 | LL5 | LL6 | LL7 | LL8 | LL9 |
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.2 | A | AA1 |
| 5.8 | C | CC2 |
| 13.3 | J | JJ5 |
| 21 | K | KK8 |
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
