vlookup question

[Fran]

in cell h13, i want to use vlookup to figure out the price of a ticket.
once i find the price of the ticket in the chart, i have to multiply it by
b7. any help????

b7 is nbr of tickets
b8 is ticket series
b9 is the location of the seat

cells a15:c20 is the price chart
example
series level 1 level 2
a $10 $5
b $15 $10

 

[Nick Hodge]

Fran

The following will work if A15:C15 are headers in your pricing matrix, if
not change the reference in the MATCH function. The IF(ISERROR...) is only
added to stop #N/A errors, you can get rid of that if you're not bothered.

=IF(ISERROR(VLOOKUP($C$7,$A$15:$C$20,MATCH($D$7,$A$15:$C$15,0),FALSE)),"",VLOOKUP($C$7,$A$15:$C$20,MATCH($D$7,$A$15:$C$15,0),FALSE)*$B$7)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Biff]

Hi!

Try this:

b9 is the location of the seat

I assume that is either Level 1 or Level 2.

series level 1 level 2

With those headers in A15:C15 -

=IF(COUNTA(B7:B9)<3,0,B7*VLOOKUP(B8,A15:C20,MATCH(B9,A15:C15,0),0))

Biff

 

[Fran]

I AM STILL HAVING PROBLEMS WITH VLOOKUP

 

[bobocat]

Hi Fran,

I assume you that only 1 or 2 will be enter in cell B9 to indicate the
location (Am I correct?)
in the simple case, no #na will appear in h13
I will check the location is level 1 or Level 2 first. If level 1, the
col-index-num will be 2, but if in level 2, then the col-index-num will be
2+1

then, in h13
=if(b9=1, vlookup(b8,a15:c20,2,false)*b7, vlookup(b8,a15:c17,2+1,false)*b7)

if #na is needed to check
in h13
=if(isna(vlookup(b8,a15:c20,1,false)),0, if(b9=1,
vlookup(b8,a15:c20,2,false)*b7, vlookup(b8,a15:c17,2+1,false)*b7))

 

[Biff]

I AM STILL HAVING PROBLEMS WITH VLOOKUP

Care to explain?

Biff