Multiple IF statements or a better method ?

[Bob Moore]

I am new to this forum and a novice with Excel especially with respect
to creating fomulas.

Help !!

I have created a VLOOKUP table that has a value grid of 7 rows x 5
columns (apartment codes x different rates depending on length of
stay). Therefore depending on the apartment code the guest wants and
their length of stay there are 35 possible results.

Using this forum, I managed (somehow) to put together a few
concatenated IF statements to point to the correct cell in the VLOOKUP
table depending on apartment code and length of stay. I now realise
that I will have to do 35 concatenated IF statements and my head is
beginning to explode just doing two !

Is there a better way ? I know thereis a limitof 7nested IF statements
but is there a limit to thenumber of concatenated IF statements (if
that is a different thing) ?

Some examples of my bodged (but working) code is :-

=IF((AND(B31>0,B31<8,F21="KOC05")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),IF((AND(B31>0,B31<8,F21="KOC03")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invalid
Input"))

p.s1
length of stays are daily, weekly, monthly, 3 monthly and 6 monthly.
ps2
administrator will just input apartment code and length of stay to
automatliccly pass rate to an invoice.

 

[daddylonglegs]

I can't quite work out your cell references but this sounds an ideal
candidate for an INDEX/MATCH formula.

If you have your 5 possible lengths of stay in B1:F1, your seven apartment
codes in A2:A8 then your 35 prices will be in B2:F8 so use this formula

=INDEX(B2:F8,MATCH(J2,A2:A8,0),MATCH(K2,B1:F1,0))

where J2 contains your specific apartment code and K2 specific length of stay

 

[Ron Rosenfeld]

I am new to this forum and a novice with Excel especially with respect
to creating fomulas.

Help !!

I have created a VLOOKUP table that has a value grid of 7 rows x 5
columns (apartment codes x different rates depending on length of
stay). Therefore depending on the apartment code the guest wants and
their length of stay there are 35 possible results.

Using this forum, I managed (somehow) to put together a few
concatenated IF statements to point to the correct cell in the VLOOKUP
table depending on apartment code and length of stay. I now realise
that I will have to do 35 concatenated IF statements and my head is
beginning to explode just doing two !

Is there a better way ? I know thereis a limitof 7nested IF statements
but is there a limit to thenumber of concatenated IF statements (if
that is a different thing) ?

Some examples of my bodged (but working) code is :-

=IF((AND(B31>0,B31<8,F21="KOC05")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),IF((AND(B31>0,B31<8,F21="KOC03")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invalid
Input"))

p.s1
length of stays are daily, weekly, monthly, 3 monthly and 6 monthly.
ps2
administrator will just input apartment code and length of stay to
automatliccly pass rate to an invoice.

Assumptions:

1. Table is F1:K8
2. Lengths of stay -- G1:K8
3. Apartment code -- F2:F8
4. (Contents of F1 is irrelevant)

A1: Apt Code
A2: Length of Stay

Formula:

=VLOOKUP(A1,F1:K8,MATCH(A2,F1:K1,0),FALSE)

should give you the result you are looking for.

The MATCH function computes the proper column for the VLOOKUP function.


--ron

 

[Ron Rosenfeld]

Assumptions:

1. Table is F1:K8
2. Lengths of stay -- G1:K8
3. Apartment code -- F2:F8
4. (Contents of F1 is irrelevant)

A1: Apt Code
A2: Length of Stay

Formula:

=VLOOKUP(A1,F1:K8,MATCH(A2,F1:K1,0),FALSE)

should give you the result you are looking for.

The MATCH function computes the proper column for the VLOOKUP function.


--ron

The above is incorrect:

Lengths of stay -- G1:K1


--ron