Nested If Statement "FALSE" Return

[Margie]

I have a table set up in excel as follows:

Town 1 2 3 4
Building 1 $12MM $12MM $8.5MM $8.5
Type 2 $10MM $10MM $6.5MM $6.5MM
3 $7.5MM $7.5MM $5.0MM $5.0MM
4 $3.0MM $3.0MM $1.0MM $1.0MM
5 $1.5MM $1.5MM $500M $500M

I've created the following formula where F25 is "town" and F24 is "building
type.

"=INDEX($Z$24:$AJ$35,MATCH(F25,$Z$24:$Z$35,),MATCHF24,$Z$24:$AJ$24,))".

I need to add a third criteria - F23 - which is "quality" - denoted by 2, 3
or 4. If it is 2, the formula noted above would work. However, if it is 3
or 4, the values in the table would change; for example, if the "quality" was
3, the "town" is 1 and the "building" is 1, the value returned would be $10.5
MM. If it was 4, the value returned would be $5MM.

I have created two additional tables that mirror the table noted above with
the exception that the values shown in each column are different; I've copied
the formula noted above into the new tables, amending the cell references to
that table (ie. @index(X45:AJ57, MATCH(F25,X45:Z57,) etc., etc. I've then
set up an @if statement which reads
"=IF(F23=2,Z37),=IF(f23=3,Z59),=if(f23=4,Z81)))". If I type in 2 in the
cell, the value returns. If I type in 3, I get "FALSE". The same with 4.
I'm stuck. I know it has something to do with the fact that there is nothing
pointing to the other two tables in the IF statement. Do I name the tables -
eg. RG2, RG3, RG4? If that's the route, can I use an IF statement? If so,
how should it read?
IF(F23=2,RG2,Z37),=IF(F23=3,RG2,Z62),IF(f23=4,RG4,Z83)))? That doesn't seem
to work - I get "your formula contains an error - check Excel Help". Help
doesn't "help" in this case. I tried to jerry rig the @index formula to
include the third criteria, but I got the FALSE return as well. I tried to
create one table with all values, but it's too big and confusing.

I would really appreciate any help you can provide. I'm sorry this is so
long winded, but I've tried to include as much detail as I could to outline
the problem.

Thanks much.

Marg

 

[Bernard Liengme]

I do not have time to test this but here is a start
You have three tables named RG2, RG3, RG4 (you have named them with Excel)
Enter the table to use in E25 (for example type: RG3)
Change formula
"=INDEX($Z$24:$AJ$35,MATCH(F25,$Z$24:$Z$35,),MATCHF24,$Z$24:$AJ$24,))".

to read "=INDEX(indirect(E25)
,MATCH(F25,indirect(E25),),MATCHF24,indirect(E25),))".

best wishes

 

[Duke Carey]

Experiment with having building type in col A and the quality indicator in
col B, along the lines of

A B
1 2
1 3
1 4
2 2
2 3
2 4

This would give you one table with 3 times as many rows - since you said you
have three separate quality indicators.

To find the correct row enter the array formula (committed by pressing
Ctrl-Shift-Enter) assuming building type in A1 and quality in B1

=match(A1&B1,A4:A15&B4:B15,0)

Just combine this with the match() you are using to get the correct town and
build the INDEX() arguments with those values

 

[Margie]

Thanks very much for your quick response, Bernard. I'm having some problems
getting the formula to work, but it's more a "user" issue than it is the
formula. I'll keep plugging away - I'm determined to get it to work!

 

[Margie]

Thanks Duke. I like your suggestion. I've set up the table as suggested;
having a bit of a problem getting the formula set up as I've not used an
array formula before. Will try to figure it out - I'm halfway there!

Cheers.

 

[Margie]

Hi Duke:
As suggested, I've set up the table as follows:

Const Grade Town Grade
1 2 3 4
2 2 $16,000,000 $16,000,000 $16,000,000
2 3 $12,000,000 $12,000,000 $12,000,000
2 4 $4,000,000 $4,000,000 $4,000,000
3 2 $12,000,000 $12,000,000 $12,000,000
3 3 $7,500,000 $7,500,000 $7,500,000
3 4 $3,500,000 $3,500,000 $3,500,000
4 2 $12,000,000 $12,000,000 $12,000,000
4 3 $7,500,000 $7,500,000 $7,500,000
4 4 $3,500,000 $3,500,000 $3,500,000

F23 = Grade
F24 = Town Grade
F25 = Construction

I've created two formula;
=INDEX($Z$24:$AJ$39,MATCH(F25,$Z$24:$Z$34,),MATCH(F24,$Z$24:$Z$39,MATCH(F23,$AA$24:$AA$39,)))
- and =INDEXZ24:AJ39,MATCHF23&F25,Z24:Z39&AA24:AA39,MATCH(F24,Z24:AJ39,)))
- neither work. One first results in an #REF! the other #VALUE!. My
understanding of formula is very basic; I've tried to figure out by examining
the structure to see where I've gone wrong but it's not apparent, to me,
anyway. I've used Excel help to see if I can correct these errors, but I
don't understand enough about the features to determine where I've gone
wrong. As mentioned, if I input a 3 into F23, a 4 into F24 and a 2 into F25,
I should get a return of $12,000,000. If I input a 4 into F23, a 2 into F24
and a 4 into F25, I should get a return of $3,500,000. It's not happening.
Can you suggest where I've gone astray?

Thanks much!

 

[Duke Carey]

Hi Margie -

Try this formula - I think you got a few of the comparisons wrong in your
version:

=INDEX($AB$25:$AD$33,MATCH(F25&F23,$Z$25:$Z$33&AA25:AA33,0),MATCH(F24,AB24:AD24))

Remember to enter it with Ctrl-Shift-Enter

 

[Margie]

Hi Duke:

Thanks for your help. It's not working - I'm getting a #value error now
which is way better than before. I'm determined to figure out what I've done
wrong - don't want to bug you again.

Have a good weekend. Cheers.

 

[Peo Sjoblom]

Value errors are derived either from calculating with text or applying a
formula over a range that already contains value error(s).

if A1:A4 holds


1
2
#VALUE!
3


=SUM(A1:A4)

will return a value error


so would

="a"+2



Are there any errors in your data?