Hi, hopefully someone can help me with this as I have drawn a blank. I have
the following rental costs options on excel
A B C D
E
1 Street Monthly Quarterly Annual Cost
2 High St Y
3 Main St Y
4 Church St Y

If I have the a reference sheet with st names and the rental costs in
columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost)
What formula can I use to get column E to "Pick" the correct Street and
costing from this data list?

On Nov 20, 9:51*am, Elaine0203
wrote:
Hi, *hopefully someone can help me with this as I have drawn a blank. *I have
the following rental costs options on excel
* * * * A * * * * * * * * * B * * * * * * * * *C * * * * * * * D * * * * * *
* * *E
1 * Street * * * * * Monthly * * * *Quarterly * * Annual * * * * * *Cost
2 * High St * * * * * * * Y
3 * Main St * * * * * * * * * * * * * * * * * Y
4 * Church St * * * * * * * * * * * * * * * * * * * * * * * * Y

If I have the a reference sheet with st names and the rental costs in
columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost) *
What formula can I use to get column E to "Pick" the correct Street and
costing from this data list?

in E2 write =vlookup(A2,F:I,2,false)

In this example you will return the Monthly cost for High St as this
is column 2 for the array. Change the 2 to a 3 or 4 to get Quarterly
or Annual respectively.

modify the the formula in cell E2 as listed below to automate the placement
of the Y in monthly or quarterly or annually:

=VLOOKUP(A2,F:I,(B2="Y")*2+(C2="Y")*3+(D2="Y")*4+A ND(B2="",C2="",D2="")*1,FALSE)

HTH
--
Data Hog


"Chuck" wrote:

On Nov 20, 9:51 am, Elaine0203
wrote:
Hi, hopefully someone can help me with this as I have drawn a blank. I have
the following rental costs options on excel
A B C D
E
1 Street Monthly Quarterly Annual Cost
2 High St Y
3 Main St Y
4 Church St Y

If I have the a reference sheet with st names and the rental costs in
columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost)
What formula can I use to get column E to "Pick" the correct Street and
costing from this data list?

in E2 write =vlookup(A2,F:I,2,false)

In this example you will return the Monthly cost for High St as this
is column 2 for the array. Change the 2 to a 3 or 4 to get Quarterly
or Annual respectively.
.

To automate the process of a "Y" being in Column B Monthly or C Quarterly or
D Annually...modify the vlookup formula as listed below.

place formula in cell E2 ( and copy down E2:E4)

=VLOOKUP(A2,F:I,(B2="Y")*2+(C2="Y")*3+(D2="Y")*4+A ND(B2="",C2="",D2="")*1,FALSE)

HTH
--
Data Hog


"Chuck" wrote:

On Nov 20, 9:51 am, Elaine0203
wrote:
Hi, hopefully someone can help me with this as I have drawn a blank. I have
the following rental costs options on excel
A B C D
E
1 Street Monthly Quarterly Annual Cost
2 High St Y
3 Main St Y
4 Church St Y

If I have the a reference sheet with st names and the rental costs in
columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost)
What formula can I use to get column E to "Pick" the correct Street and
costing from this data list?

in E2 write =vlookup(A2,F:I,2,false)

In this example you will return the Monthly cost for High St as this
is column 2 for the array. Change the 2 to a 3 or 4 to get Quarterly
or Annual respectively.
.

Thank you both for your help. Worked perfectly - such a relief!!



"J_Knowles" wrote:

To automate the process of a "Y" being in Column B Monthly or C Quarterly or
D Annually...modify the vlookup formula as listed below.

place formula in cell E2 ( and copy down E2:E4)

=VLOOKUP(A2,F:I,(B2="Y")*2+(C2="Y")*3+(D2="Y")*4+A ND(B2="",C2="",D2="")*1,FALSE)

HTH
--
Data Hog


"Chuck" wrote:

On Nov 20, 9:51 am, Elaine0203
wrote:
Hi, hopefully someone can help me with this as I have drawn a blank. I have
the following rental costs options on excel
A B C D
E
1 Street Monthly Quarterly Annual Cost
2 High St Y
3 Main St Y
4 Church St Y

If I have the a reference sheet with st names and the rental costs in
columns F (st names) G (monthly cost) H (quarterly cost) I (annual cost)
What formula can I use to get column E to "Pick" the correct Street and
costing from this data list?

in E2 write =vlookup(A2,F:I,2,false)

In this example you will return the Monthly cost for High St as this
is column 2 for the array. Change the 2 to a 3 or 4 to get Quarterly
or Annual respectively.
.