VLOOKUP with more than 7 levels of IF

G

Guest

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=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!
 
G

Guest

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)
 
G

Guest

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 said:
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 said:
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=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!
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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=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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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!G5:p21,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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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!G5:p21,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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Duke Carey said:
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 said:
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 said:
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!G5:p21,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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if function (if more that 7 nesting) 2
INDIRECT(ADDRESS) inside a VLOOKUP 13
Vlookup array formula problem 2
use more than 7 if condition in a cell 8
Multiple levels of sumif? 3
Nesting more than 7 IF statements (Excel 2003) 7
Array formula using sumproduct & vlookup 6
returning more columns with vlookup? 2

Top