Please help!

[Elaine0203]

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?

 

[Chuck]

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.

 

[J_Knowles]

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+AND(B2="",C2="",D2="")*1,FALSE)

HTH

 

[J_Knowles]

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+AND(B2="",C2="",D2="")*1,FALSE)

HTH

 

[Elaine0203]

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