Please help!

E

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?
 
C

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help! 2
help w indirect and index,match 3
Help for Excel time format "MACRO" 6
A Huge Conditional Format Challenge.... 2
Centralized data 3
new user needs help with programing 4
Combobox Help 1
Query 1

Top