VLOOKUP with more than 7 levels of IF

[AppraiserRon]

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!G521,IF(AND(Dropdown4=Buildings!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!

 

[Duke Carey]

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]

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.

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)

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!G521,IF(AND(Dropdown4=Buildings!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!

 

[Duke Carey]

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.

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.

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)

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!G521,IF(AND(Dropdown4=Buildings!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!

 

[AppraiserRon]

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

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.

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.

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)



:

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!G521,IF(AND(Dropdown4=Buildings!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!

 

[Duke Carey]

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.

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

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.

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.



:

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)



:

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!G521,IF(AND(Dropdown4=Buildings!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!

 

[AppraiserRon]

Thanks, Duke. You were very helpful! I got it! That really works slick!

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.

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

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.

:

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.



:

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)



:

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!G521,IF(AND(Dropdown4=Buildings!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!