Nested Formula?

[Guest]

I am trying to create a formula and I am stuck.

For example:

If cell A1 is “240†then use the rates from the “Rates!†sheet cells
A1!:B11!, If cell A1 is “600†then use the rates from the “Rates!†sheet
cells C1!11!

All of this depends on what is in cell A2

The formula I used was:

IF$A1=240,IF($A2>0,VLOOKUP($G9,Rates!$A$1:$B$11,2)),$A1=600,if($A2>0,vlookup($G9,rates!$C$1:$D$11,0)))

Is it possible to write a formula like this? I just keep getting error
messages. Any suggestions would be greatly appreciated

 

[Pete_UK]

I think you have missed an IF from the middle, and your second VLOOKUP
is missing a column_ref. As the A2>0 condition applies to both, you
might like to try this variation:

=IF($A2>0,IF($A1=240,VLOOKUP($G9,Rates!$A$1:$B$11,2,0),IF($A1=600,VLOOKUP($G9,Rates!$C$1:$D$11,2,0),0)),0)

I have assumed you want a result of 0 if A2 is not greater than 0 (last
parameter).

Hope this helps.

Pete

 

[Guest]

What kind of error messages? What is A2 doesn't equal 240 OR 600? Is the
value in A2 a text presentation of a number, or is it really a number? If it
is text, you'll need to test for A2="240"

If the only 2 POSSIBLE values in A2 are 240 & 600 you can try

=VLOOKUP($G9,if(a2=240,Rates!$A$1:$B$11,rates!$C$1:$D$11),2,0)

 

[Guest]

"The formula you typed contains and error" info box.

The value in A2 will either be 240 or 600. If it is not either of those two
numbers then it should be left blank. It is an actual number manually
entered.

I tried yours and recieved #N/A. I dont see in your formula if 240 do this
and if 600 do that. Maybe I am missing something?

 

[Guest]

I recieved a "0".

Here is the actual formula that I used

IF($J10>0,IF($J9=240,VLOOKUP($G9,Rates!$A$3:$B$11,2,0),IF($J9=600,VLOOKUP($G9,Rates!$D$3:$E$11,2,0),0)),0)

J10 has a value of 0, 1, 2,3 or 4
J9 has a value of 240, 600 or "Decline"
G9 has an age formula in it =ROUND((($I$1-J8)-185)/(365),0)

Based on the age that is in G9 and the number that is in J9 and J10 will
determine the rate used in A3:B11 and D3:E11

I hope I am explaining myself clearly.

 

[Guest]

My formula was offered if the ONLY values possible were 240 and 600. In that
case, if it isn't 240, it MUST BE 600. Since there is another possibility,
use this instead:

=IF($A1=240,VLOOKUP($G9,Rates!$A$1:$B$11,2,0),if($A1=600,vlookup($G9,rates!$C$1:$D$11,2,0),"Decline"))

Given the error you got, I'd say that the lookup value didn't appear in your
lookup table. You will get the #N/A error if you use FALSE or zero as the
4th argument to VLOOKUP. Without that argument, VLOOKUP gives you an
approximate answer.

 

[Guest]

Sorry to keep asking for help. I used the following

=IF($J9=240,VLOOKUP($G9,Rates!$A$3:$B$11,2,0),IF($J9=600,VLOOKUP($G9,Rates!$D$3:$E$11,2,0),"Decline"))

Still recieving #N/A

Do you think I am recieving an error since G9 has a formula
=ROUND((($I$1-J8)-185)/(365),0) instead of an actual number?

 

[Guest]

I think there is still one part missing from the formula....the J10 cell

=IF($J9=240,VLOOKUP($G9,Rates!$A$3:$B$11,2,0),IF($J9=600,VLOOKUP($G9,Rates!$D$3:$E$11,2,0),"Decline"))

Based on the info in J10 (the numbers 0,1,2,3,or 4) will determine what rate
to use on the Rate! sheet. That was why in my original formula I had
IF$A1=240,IF($A2>0
meaning if it was more then 0 then use use the rates from the rage a3;b11 on
Rates! spreadsheet

Rates spreadsheet looks like this

1 $244.40
30 $321.84
40 $385.30
45 $434.98
50 $519.24
55 $620.80
60 $779.67
65 $856.39
70 $943.51
2 $410.33
3 $367.73
4 $612.87

Does this make sense?

 

[Guest]

No - you're getting #N/A because the formula is returning a value that isn't
in your lookup table. For instance, you table has lookup values of 1,3,5,7
but your formula calculates to a 4. 4 isn't in the lookup table, so Excel
gives you the #NA

You can change the VLOOKUPs to be VLOOKUP($G9,Rates!$A$3:$B$11,2) [dropping
the ,0 at the end]. Using the example of trying to look up the value 4,
Excel will now return the lookup table's value for 3 - the highest value that
doesn't exceed the 4 you're trying to look up

From the Help file

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array.
Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a
reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must
be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from
the Data menu and selecting Ascending.

The values in the first column of table_array can be text, numbers, or
logical values.

Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching
value must be returned. A col_index_num of 1 returns the value in the first
column in table_array; a col_index_num of 2 returns the value in the second
column in table_array, and so on. If col_index_num is less than 1, VLOOKUP
returns the #VALUE! error value; if col_index_num is greater than the number
of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If
FALSE, VLOOKUP will find an exact match. If one is not found, the error value
#N/A is returned.