Help to build a Look Up Function or What Ever Function Excel 2002

C

Carlo

I am a limo driver and I have bulit a extensive monthly spread sheet. Each
monthly book has 5 to 6 different spread sheets within it. (useless info, any
ways) Each spread sheet is based on my weekly runs. I will do my best to
explain.

B7 C7 D7 E7 F7

Event From To Car Time
Departure Ancaster Toronto Sedan $20.00

What I need is a Look Up or what ever will work. Below is a table of our
price list according to the event and type of car we use. Based on the
results above and the info I have inputted the function to read the table
below and place the price in the "Time" column

Weddings and Socials are based on a hourly rate which needs to be multipled
using the results in N7

VAN BUS
CAR LIMO SUV
Airport $20 $22 $30
Airport $30 $30 $40
Bar Run $50 $60 $70
Bar Run $50 $60 $70

Add'l $2 $3 $5
Social $10 $10 $12
Wedding $10 $10 $12
Prom $30 $40 $50
Prom $40 $40 $70

Airport $12 $12 $15
Airport $25 $25 $30
Airport $30 $30 $40
Airport $30 $30 $40
Airport $30 $30 $40
Airport $30 $30 $40
Airport $35 $35 $45
Airport $35 $35 $45
Airport $40 $40 $50
Airport $40 $40 $50
Airport $40 $40 $50
Airport $40 $40 $50
Airport $40 $40 $50

Bar Run $30 $40 $50
Bar Run $30 $50 $50
Bar Run $30 $50 $50
Bar Run $40 $50 $50
Bar Run $60 $70 $80
Bar Run $60 $70 $80
 
A

akphidelt

Well first off, you need some kind of unique identifier in your table. It
could be as easy as number them 1,2,3,etc. Or you can do a numbering system
that gives you an idea of what it means like Air.Event1, Air.Event2, etc.

So then you could use a VLookup function in the Time column, like

Vlookup(Event,YourTableList,ColNumber,FALSE)

So you need to create a new column that determines what Column should be
selected in the vlookup. For instance insert a new column F

If(E7="Car",1,IF(E7="Limo",2,If(E7="SUV",3,"N/A")))

So your final vlookup in the time column would be

Vlookup(B7,YourListArray,F7,FALSE)

I might be totally off here, but let me know if you have any questions.
 
C

Carlo

I am quite lost. Do you know of any one interested in completing this task
for about $50 to $75 (e-mail address removed)
 
C

Carlo

Well first off, you need some kind of unique identifier in your table. WHICH
TABLE the one with the prices or the one that has the event, car type..... a
little confused, did you get my last response.
 
A

akphidelt

Yea, I can set something up for you. The unique identifier was for the table.
You have to be able to reference the table by an identifier so you can get
the correct cost for that event. So if you have

Airport $20 $22 $30
Airport $25 $30 $50

Excel has no way of determining which Airport you want to choose. You need
something like Airport.OneWay, Airport.RoundTrip, etc. Then when you want to
create the look up function you have something for excel to actually look up.
 
C

Carlo

Actually the Airport for Arrival Or Departure are the same price, the only
difference between each is from what city you are leaving from or to and or
what type of car you are using. I would really like to move on this, here is
my email address: (e-mail address removed)

I look forward in hoping to complete this, as soon as possible.

Thank You
Carlo
 
C

Carlo

Sir I would really like to proceed

Carlo said:
Well first off, you need some kind of unique identifier in your table. WHICH
TABLE the one with the prices or the one that has the event, car type..... a
little confused, did you get my last response.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top