Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Function
I am creating a invoice for an Theatre to calculate price of ticket based on
location(Balcony, Orchestra) and series (A, B, C. D. E. F). How do I get the ticket price to display the correct price based on the two factors above? Ex. # of seats: 4 Series: A Location: Balcony Balcony prices a 100, 200, 300 Orchestra prices a 400, 500, 600 I can only get it to come up with the correct answer for Orchestra or Balcony but not both. My formula is as follows: =VLOOKUP(Series,PriceTable,2)*NbrTickets |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Function
Hi
Assuming Price_Table has 3 columns headed Series, Balcony and Orchestra and below this 6 rows with A through F in the next 6 cells below Series, and relevant prices in the other columns. Then your formula should be =IF(Location="Balcony",VLOOKUP(Series,Price_Table, 2), VLOOKUP(Series,Price_Table,3))*NbrTickets Alternatively, if your Price table were in cells A1:C7, Name A1:C1 as MyLocation, Name A1:A7 as MySeries then =INDEX(Price_Table,MATCH(Series,MySeries,0),MATCH( Location,MyLocation,0))*NbrTickets change to suit locations as required. -- Regards Roger Govier "Brain Freeze" <Brain wrote in message ... I am creating a invoice for an Theatre to calculate price of ticket based on location(Balcony, Orchestra) and series (A, B, C. D. E. F). How do I get the ticket price to display the correct price based on the two factors above? Ex. # of seats: 4 Series: A Location: Balcony Balcony prices a 100, 200, 300 Orchestra prices a 400, 500, 600 I can only get it to come up with the correct answer for Orchestra or Balcony but not both. My formula is as follows: =VLOOKUP(Series,PriceTable,2)*NbrTickets |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Function
Mr. Roger Govier,
You are a saint. Thank you so much for all of you help. It works perfectly thanks to you. Ms. Mathis (Brain Freeze) "Roger Govier" wrote: Hi Assuming Price_Table has 3 columns headed Series, Balcony and Orchestra and below this 6 rows with A through F in the next 6 cells below Series, and relevant prices in the other columns. Then your formula should be =IF(Location="Balcony",VLOOKUP(Series,Price_Table, 2), VLOOKUP(Series,Price_Table,3))*NbrTickets Alternatively, if your Price table were in cells A1:C7, Name A1:C1 as MyLocation, Name A1:A7 as MySeries then =INDEX(Price_Table,MATCH(Series,MySeries,0),MATCH( Location,MyLocation,0))*NbrTickets change to suit locations as required. -- Regards Roger Govier "Brain Freeze" <Brain wrote in message ... I am creating a invoice for an Theatre to calculate price of ticket based on location(Balcony, Orchestra) and series (A, B, C. D. E. F). How do I get the ticket price to display the correct price based on the two factors above? Ex. # of seats: 4 Series: A Location: Balcony Balcony prices a 100, 200, 300 Orchestra prices a 400, 500, 600 I can only get it to come up with the correct answer for Orchestra or Balcony but not both. My formula is as follows: =VLOOKUP(Series,PriceTable,2)*NbrTickets |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP Function
You are a saint
The halo's shining, though many wouldn't agree<g You're very welcome. Thanks for the feedback. Glad it worked out for you. -- Regards Roger Govier "Brain Freeze" wrote in message ... Mr. Roger Govier, You are a saint. Thank you so much for all of you help. It works perfectly thanks to you. Ms. Mathis (Brain Freeze) "Roger Govier" wrote: Hi Assuming Price_Table has 3 columns headed Series, Balcony and Orchestra and below this 6 rows with A through F in the next 6 cells below Series, and relevant prices in the other columns. Then your formula should be =IF(Location="Balcony",VLOOKUP(Series,Price_Table, 2), VLOOKUP(Series,Price_Table,3))*NbrTickets Alternatively, if your Price table were in cells A1:C7, Name A1:C1 as MyLocation, Name A1:A7 as MySeries then =INDEX(Price_Table,MATCH(Series,MySeries,0),MATCH( Location,MyLocation,0))*NbrTickets change to suit locations as required. -- Regards Roger Govier "Brain Freeze" <Brain wrote in message ... I am creating a invoice for an Theatre to calculate price of ticket based on location(Balcony, Orchestra) and series (A, B, C. D. E. F). How do I get the ticket price to display the correct price based on the two factors above? Ex. # of seats: 4 Series: A Location: Balcony Balcony prices a 100, 200, 300 Orchestra prices a 400, 500, 600 I can only get it to come up with the correct answer for Orchestra or Balcony but not both. My formula is as follows: =VLOOKUP(Series,PriceTable,2)*NbrTickets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can we use multiple if with VLookup function | Excel Worksheet Functions | |||
vlookup Function Help Needed | Excel Worksheet Functions | |||
Pastable function using VLOOKUP? | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions |