VLOOKUP Function

[Guest]

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 are: 100, 200, 300
Orchestra prices are: 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

 

[Roger Govier]

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.

 

[Guest]

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]

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.