Help creating a VLookup table?

Dear All

I have problem in designing my Vlook up table, I pull up information from my internal Data base- In this data base there are Various column from A-R
ColumnA giving the name of the deport ,column B Route number ,column C fare rate,coulmn D fare stage number, and others to column R

What I need to do is that to allocate number of passengers boarding on a bus at each fare stage number by borough council-the routes operates across borough council

I create another table , I named it as Borough council master file, in this file there are three column A-C, Column A Route number, Column B Fare stage number, and Column C Borough council name

What I need to do is to Create V look up table and link these two sheets , then run Pivot table to get number of passengers by Borough councils

The question is how to write the V look up formular ? so as I can get the number of passengers by Borough council

I need your help

Thank you


Wahiba

Originally Posted by wahiba

Dear All

I have problem in designing my Vlook up table, I pull up information from my internal Data base- In this data base there are Various column from A-R
ColumnA giving the name of the deport ,column B Route number ,column C fare rate,coulmn D fare stage number, and others to column R

What I need to do is that to allocate number of passengers boarding on a bus at each fare stage number by borough council-the routes operates across borough council

I create another table , I named it as Borough council master file, in this file there are three column A-C, Column A Route number, Column B Fare stage number, and Column C Borough council name

What I need to do is to Create V look up table and link these two sheets , then run Pivot table to get number of passengers by Borough councils

The question is how to write the V look up formular ? so as I can get the number of passengers by Borough council

I need your help

Thank you


Wahiba

Are you sure you really need to use a VLOOKUP?
Why can't you create the Pivot table directly from your Database columns A-R?

When you think about it, a pivot table is just a smart way of doing several individual routines/processes, extracting unique field values, MATCH, VLOOKUPS, SUMIFs, COUNTIFs etc.

Post back if I've misunderstood. An attached zipped copy of your workbook would also be useful.

Rgds

Hi RIchard

Thank you very much for your help in moving my query in a proper place for other people to view

lets me explain more to you,the data base has got no Authority area names , therefore, I have to use V Look up to ADD Authority names in a data base so as I can run the Pivot table

My problem is ,I have three columns, the route number, the stage number and the authority name - the question is How can I write a formular to link up these three columns with the original data base

I'm thinking of using concetenate to add up together Route number and stage number into one column and authority area on the other column then use normal V Look up formular

Any idea please email to me


Thank you

wahiba

Originally Posted by wahiba

Hi RIchard

Thank you very much for your help in moving my query in a proper place for other people to view

lets me explain more to you,the data base has got no Authority area names , therefore, I have to use V Look up to ADD Authority names in a data base so as I can run the Pivot table

My problem is ,I have three columns, the route number, the stage number and the authority name - the question is How can I write a formular to link up these three columns with the original data base

I'm thinking of using concetenate to add up together Route number and stage number into one column and authority area on the other column then use normal V Look up formular

Any idea please email to me


Thank you

wahiba

Ah OK, understood. Incidentally it was not me who moved your post but presumably one of the site moderators.

You're on the right lines since you'll need to use the route number and stage number together as the subject in a VLOOKUP(). Your only problem will be if two authorities happen to have the same route number and stage number. In that case you would have to find some other unique characteristic in your database and incorporate this in your VLOOKUP(). But for the moment let's assume this isn't the case.

Create a new first column in your Route/Stage/Authority table and enter the following in A1 and copy down

Please Login or Register  to view this content.

Give a range name to this four column table, say 'Routes'

Now in your database, add a temporary new helper column, say column A and concatenate the Route and Stage fields together much like you've done with the Routes Table above.

Now add a new column wherever you want for the Authority, and enter the following formula, copying down as far as necessary.

Please Login or Register  to view this content.

Convert all the formulae back to Values with Edit..Copy..PasteSpecial..Values and delete the temporary column A in your database

Rgds