help with vlookup

[Guest]

Help - I need to compute a formula to determine the price of a ticket. Cell H5

In Cell b1 is # of tickets needed
In cell B2 is drop down list for the type of seating ranging from A - F -
this field is also labled Series
In cell B3 is a Drop down list asking for Location - with two choices Upper
or Lower - this field is Called Location

In a named Range A14 - C21, called Price Table, is the array of info as
follows
a14 - Series - b14 - Upper - C14 - Lower
a15 = A b15 = 170 c15 = 225
a16 = b b16 = 120 c16 = 220
a17 = c c16 = 100 c17 = 190

etc.......

I thought I could use Vlookup - but I keep receiving an error

what formual can I use that I would insert into Cell H5 - that would look at
cell B2 and distinguis which type of series is selected
then Look in cell B3 to look at the Location
Then to find that information from the table

and then multiply it by cell b1, the number of tickets needed.

I hope I explained this correctly. It should be easily done - I don't know
what I am doing wrong.

Thanks

 

[L. Howard Kittle]

Try something like this. If "Upper" is selected the lookup is in the 2nd
column, if it is NOT Upper (then it must be "Lower") the formula looks up
3rd column.

=IF(B3="Upper",VLOOKUP(B2,Price_table,2,0),VLOOKUP(B2,Price_table,3))

If you are still struggling, you can send me a sample workbook and I will
give it a go.

HTH
Regards,
Howard

 

[L. Howard Kittle]

Whoops... forgot to multiply for number of tickets.

add this to the end of the formula *B1

Regards,
Howard

 

[Guest]

Thanks Howard,

This did not work but it did put me on the right track - I was unable to use
the Defined Names within my vlookup - once I hardcoded the range in -
overriding excels default vlookup wizard names - it seemed to work...imagine
that??

Thanks again for all your help!