VLOOKUP Query

Hi,
I'm quite new to using formulas on Excel and am trying to work out what is probably a simple fix.
I've set up a very basic Excel spreadsheet as an example to show what I am trying to do.
On the sheet I have several headed columns, and then starting in row F I have the days listed Monday-Sunday.
I want to show which route number each delivery location ran on for each day and show the results in the Monday-Sunday columns.
The problem I am having is when I use my VLOOKUP formula as shown in the screenshot it works for the first entry (Monday) but when I drag it across the other day's I get incorrect data or errors.
I know that the formula will give answers when going down a column but how do I get it to populate the correct information going across on the sheet.
I know its probably something simple but as I say I'm new to Excel and teaching myself how to use the formulas etc so any help anybody can give would be very much appreciated.
Many Thanks

Adam
excel example.jpg

Hello
Try referencing F11 and across as your lookup value, for example:

=VLOOKUP(F11,$A$4:$E$10,4,0)

Also lock in your lookup table with absolute references ($).

Hope this helps.
DBY

Many thanks DBY, I knew it would be something simple, you have helped me a lot.
My next question if you dont mind is, If I had multiple delivery locations and a list of the delivery locations running down the sheet starting in column E12 how would I get the formula to populate the information for each delivery location for each day?

Thanks again in advance
Adam

Glad I could help

If you want the Location returned for each day you just have to reference column 3 of the lookup table using the same formula. Is that what you're looking to do?

=VLOOKUP(F11,$A$4:$F$10,3,0)

I have attached an updated screenshot for you.
I have added extra delivery locations and added those locations into my results table for Monday-Sunday.
I now want to be able to get the information for Avonmouth, Harlow and Didcot to show which route number each location used for each day.
So its the same information as before but for multiple locations.
Hope that makes sense

Thanks Again
Adam
excel example2.JPG

My first impression is that a pivot table will provide you the desired result

Originally Posted by bstoyano

My first impression is that a pivot table will provide you the desired result

Hello and thanks for your reply
I am very new to Excel and haven't used pivot tables before either.
If you could point me in the right direction for information on pivot tables I would again be very grateful.

Thanks

Adam

sure,
1. Mark the area with the data (make sure each column in your selected range has a title, as the first row will be used for column headings)
2. Somewhere in the menus you have Pivot Table (I am using Excel 2003 on the machine at the moment, so I cannot be more specific now), click Insert Pivot and then add Day to the Column area, Route and Location to the Row Area, and in the Aggregation area you should add the field you want to, well, aggregate. adn specify the aggegation type (sum, average, count...)
Hope to have helped.

Originally Posted by bstoyano

sure,
1. Mark the area with the data (make sure each column in your selected range has a title, as the first row will be used for column headings)
2. Somewhere in the menus you have Pivot Table (I am using Excel 2003 on the machine at the moment, so I cannot be more specific now), click Insert Pivot and then add Day to the Column area, Route and Location to the Row Area, and in the Aggregation area you should add the field you want to, well, aggregate. adn specify the aggegation type (sum, average, count...)
Hope to have helped.

Many thanks,
Everybody has been very helpful

Adam
bstoyano is correct a Pivot would seem to work well but if you want a formula solution, and your layout is consistent with each combination of Day and Location unique, then the following formula should work:

=INDEX($C$4:$C$24,MATCH($E28,$B$4:$B$24,0)-1+COLUMNS($F$28:F28))

Copied across and down F28:L30 in your example.

If you want information on building Pivot tables in 2010, take a look a this video and others like it on YouTube:

http://www.youtube.com/watch?v=7MntP4s5bhs

DBY

Originally Posted by DBY

Adam
bstoyano is correct a Pivot would seem to work well but if you want a formula solution, and your layout is consistent with each combination of Day and Location unique, then the following formula should work:

=INDEX($C$4:$C$24,MATCH($E28,$B$4:$B$24,0)-1+COLUMNS($F$28:F28))

Copied across and down F28:L30 in your example.

If you want information on building Pivot tables in 2010, take a look a this video and others like it on YouTube:

http://www.youtube.com/watch?v=7MntP4s5bhs

DBY

Thank you very much,
I am going to work through the formula you have given me so that I understand why I am entering what I am.
I will also take a look at the information on Pivot Tables.
Thanks again
Adam

Just as a correction to my last post. I'd forgotten to a include a 'Temp' column on my own test sheet so the formula should be adjusted 1 column:

=INDEX($D$4:$D$24,MATCH($F28,$C$4:$C$24,0)-1+COLUMNS($G$28:G28))

DBY