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(B310,B31<8,F21="KOC05")),VLOOKUP(F21,Pri ces!A3:C10,3,FALSE),IF((AND(B310,B31<8,F21="KOC03 ")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invali d 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.

Please ignore my code sample os the copy and paste somehow got all screwed up.

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

"Bob Moore" wrote:


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(B310,B31<8,F21="KOC05")),VLOOKUP(F21,Pri ces!A3:C10,3,FALSE),IF((AND(B310,B31<8,F21="KOC03 ")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invali d
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.




--
Bob Moore

On Fri, 24 Nov 2006 21:06:47 +0000, Bob Moore
wrote:


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(B310,B31<8,F21="KOC05")),VLOOKUP(F21,Pr ices!A3:C10,3,FALSE),IF((AND(B310,B31<8,F21="KOC0 3")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invali d
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

On Fri, 24 Nov 2006 19:33:48 -0500, Ron Rosenfeld
wrote:

On Fri, 24 Nov 2006 21:06:47 +0000, Bob Moore
wrote:


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(B310,B31<8,F21="KOC05")),VLOOKUP(F21,P rices!A3:C10,3,FALSE),IF((AND(B310,B31<8,F21="KOC 03")),VLOOKUP(F21,Prices!A3:C10,3,FALSE),"Invali d
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

The above is incorrect:

Lengths of stay -- G1:K1


--ron