VLOOKUP question concerning population a price

[DLL]

If I had a seat list and price list according to age;

Lets say 18 and over is $30.00 and under 18 is $20.00 that is for a floor
level seat. A balcony seat is $15.00 if you are 18 and over. It is $10.00 if
you are under 18. Then you have a front row seat that is $40.00 dollars if
you are 18 and over and $35.00 if you are under 18.

I have to determine the price according to age and seat selection.

Any help is sure appreciated I am missing some step but can not figure out
where. I hope I have given enough information. If not let me know...THANKS

 

[DLL]

I meant to type "Population a price"

 

[Max]

One simple way is to set-up a "cross-tab" price table,
then use index/match

Assume the price table below is set-up in A1:C4

PriceTable 0 18
Front 35 40
Balcony 10 15
Floor 20 30

Then if you have the age/seat running in E2:F2 down, eg:

12 Balcony
19 Front
18 Floor

you could place this in G2:
=IF(COUNTA(E2:F2)<2,"",INDEX($B$2:$C$4,MATCH(F2,$A$2:$A$4,0),MATCH(E2,$B$1:$C$1)))
and copy G2 down to return the correct prices, viz.:

12 Balcony 10
19 Front 40
18 Floor 30

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[smartin]

I meant to type "Population a price"

One way:

Make a little table like this in Sheet1!A1:C4

18+ under 18
Floor 30 20
Balcony 15 10
Front Row 40 35

In Sheet2 set up column labels and search criteria, e.g.:

Seating Age Price
Balcony under 18 ?

The ? formula in C2 is

=INDEX(Sheet1!$B$2:$C$4,MATCH(Sheet2!A2,Sheet1!$A$2:$A$4,0),MATCH(Sheet2!B2,Sheet1!$B$1:$C$1,0))

 

[DLL]

Thanks but I actually need to use the VLOOKUP formula, I had success doing it
the way you described.

 

[DLL]

Thanks that seems to work, but I really need to use the VLOOKUP formula. It's
for a class. Not the exact problem but similar. Thanks for any help

 

[smartin]

Ok then. Here are several hints (^:

First, you know that VLOOKUP requires a single column of values to serve
as a "key", right? But you have /two/ criteria that make up the key
(Seating and Age). What to do? Here's what...

Revise the little table in Sheet1 so it looks like this:

A B C D
Seating Age Key Price
Floor 18OO ? 30
Floor U18 ? 20
Balcony 18OO ? 15
.....

The first formula ? is
= A2&B2

Now, in Sheet2, you also have columns for

A B C D
Seating Age ... Price

Can you see what to do next?

N.B. I changed the Age values to simplify thing a little.

 

[T. Valko]

I really need to use the VLOOKUP formula.

It's for a class.

Using Max's table in A1:C4

Lookup values:

A10 = location
B10 = age

=VLOOKUP(A10,A1:C4,MATCH(B10,A1:C1),0)

 

[DLL]

Any way to talk in a formula? I'm a little green in excel. Thanks for your
help, I know it's a pain.