Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP with more than 7 levels of IF
My table has 19 rows and 11 columns. The leftmost column is a list of 17
buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#2
|
|||
|
|||
Create another lookup table that concatenates the Utility & Condition values,
listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#3
|
|||
|
|||
Thank you, Duke. I understand your suggestion up to the VLOOKUP formula, I
am fuzzy on how the col# in the new table connects to the original table. Thanks for the help. "Duke Carey" wrote: Create another lookup table that concatenates the Utility & Condition values, listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#4
|
|||
|
|||
It appeared as though the result of your IF() statement was the column # that
the original VLOOKUP() should use. That's the number that ought to go into the second column of the new lookup table. Hope that explains my intent. Post back if not. "AppraiserRon" wrote: Thank you, Duke. I understand your suggestion up to the VLOOKUP formula, I am fuzzy on how the col# in the new table connects to the original table. Thanks for the help. "Duke Carey" wrote: Create another lookup table that concatenates the Utility & Condition values, listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#5
|
|||
|
|||
Duke,
Your intent is correct, but my understanding is limited. Let me see if I can restate the intent. My original VLOOKUP formula finds the type of building in the original table based on the entry in a cell outside of the original table. That cell is the lookup-table value that is first in the equation, lookup_table. The second value in the VLOOKUP formula is the original table, the table_array. The third value is the col_index_num. I understand your suggestion to mean to place the new vlookup formula here to define the column index number in the original table. Is that correct? If so, please explain your formula so I can grasp it with my limited understanding. Ron "Duke Carey" wrote: It appeared as though the result of your IF() statement was the column # that the original VLOOKUP() should use. That's the number that ought to go into the second column of the new lookup table. Hope that explains my intent. Post back if not. "AppraiserRon" wrote: Thank you, Duke. I understand your suggestion up to the VLOOKUP formula, I am fuzzy on how the col# in the new table connects to the original table. Thanks for the help. "Duke Carey" wrote: Create another lookup table that concatenates the Utility & Condition values, listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#6
|
|||
|
|||
first lookup table:
Good Good Fair Fair Poor Poor Util 1 Util2 Util1 Util2 Util1 Util2 Bldg1 Bldg2 .... Bldgn second lookup table Util1 - Good 2 Util2 - Good 3 Util1 - Fair 4 Util2 - Fair 5 etc... =VLOOKUP("Bldg2",table1,VLOOKUP("Util1 - Fair",table2,2)) The SECOND VLOOKUP in that formula returns a 4, telling the first VLOOKUP() to pull the value in the 4th column of the appropriate row. "AppraiserRon" wrote: Duke, Your intent is correct, but my understanding is limited. Let me see if I can restate the intent. My original VLOOKUP formula finds the type of building in the original table based on the entry in a cell outside of the original table. That cell is the lookup-table value that is first in the equation, lookup_table. The second value in the VLOOKUP formula is the original table, the table_array. The third value is the col_index_num. I understand your suggestion to mean to place the new vlookup formula here to define the column index number in the original table. Is that correct? If so, please explain your formula so I can grasp it with my limited understanding. Ron "Duke Carey" wrote: It appeared as though the result of your IF() statement was the column # that the original VLOOKUP() should use. That's the number that ought to go into the second column of the new lookup table. Hope that explains my intent. Post back if not. "AppraiserRon" wrote: Thank you, Duke. I understand your suggestion up to the VLOOKUP formula, I am fuzzy on how the col# in the new table connects to the original table. Thanks for the help. "Duke Carey" wrote: Create another lookup table that concatenates the Utility & Condition values, listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
#7
|
|||
|
|||
Thanks, Duke. You were very helpful! I got it! That really works slick!
"Duke Carey" wrote: first lookup table: Good Good Fair Fair Poor Poor Util 1 Util2 Util1 Util2 Util1 Util2 Bldg1 Bldg2 ... Bldgn second lookup table Util1 - Good 2 Util2 - Good 3 Util1 - Fair 4 Util2 - Fair 5 etc... =VLOOKUP("Bldg2",table1,VLOOKUP("Util1 - Fair",table2,2)) The SECOND VLOOKUP in that formula returns a 4, telling the first VLOOKUP() to pull the value in the 4th column of the appropriate row. "AppraiserRon" wrote: Duke, Your intent is correct, but my understanding is limited. Let me see if I can restate the intent. My original VLOOKUP formula finds the type of building in the original table based on the entry in a cell outside of the original table. That cell is the lookup-table value that is first in the equation, lookup_table. The second value in the VLOOKUP formula is the original table, the table_array. The third value is the col_index_num. I understand your suggestion to mean to place the new vlookup formula here to define the column index number in the original table. Is that correct? If so, please explain your formula so I can grasp it with my limited understanding. Ron "Duke Carey" wrote: It appeared as though the result of your IF() statement was the column # that the original VLOOKUP() should use. That's the number that ought to go into the second column of the new lookup table. Hope that explains my intent. Post back if not. "AppraiserRon" wrote: Thank you, Duke. I understand your suggestion up to the VLOOKUP formula, I am fuzzy on how the col# in the new table connects to the original table. Thanks for the help. "Duke Carey" wrote: Create another lookup table that concatenates the Utility & Condition values, listing all possible combinations, & provides the column # of your original lookup table. So, the table should be "Utility1 - Good" col# "Utility1 - Average" col# "Utility1 - Fair" col# "Utility2 - Good" col# "Utility2 - Average" col# "Utility2 - Fair" col# Then, instead of multiple ifs, use VLOOKUP(utility value&" - "&condition value,lookup2,2) "AppraiserRon" wrote: My table has 19 rows and 11 columns. The leftmost column is a list of 17 buildings. The second column is a unit type (sq. ft.). I have 9 columns with numbers. The top two rows are descriptions of Utility and Condition (Good, Average, Fair, etc.) Using VLOOKUP, IF and AND, I can nest functions to use 7 of the 9 columns. VLOOKUP(Text81,Buildings!G5:P21,IF(AND(Dropdown4=B uildings!P3,D32=Buildings!P4),10, IF ................[ET CETERA] I need at least to cover the 9 combinations of Utility and Condition but I've met the limit of my ability. Thank you for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Vlookup | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |