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.
.