vlookup problems

[anon125]

does it not like dates?
i have columns of data and i wish to search it for each month and then print a months worth.
here is part of the data. maybe vlookup does not like dates?
i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"

thanks all

[etaf]

L2 is not a date, if you format as a date , it will work, and you can format the cell to just show the month

"jun" is text and not a date

also you are looking up dates in tides which some are the same, and vlookup will only lookup the first matching value

what exactly are you trying to achieve?

[newdoverman]

Maybe Pivot Tables will accomplish what you want. I have added two Pivot Tables to your file.

[anon125]

thanks all
I have no idea what to do with a pivot table.
i just want to copy and rearrange the data so each month will print on one page.
suggestions welcome
thanks again

[anon125]

well i have tried your pivot table and consulted the excel bible...
so how do i get each month on one page each?
like the ones included in the worksheet. 3 columns.
thanks all

[newdoverman]

On the Pivot Table, the first column labelled Month, there is a down arrow for the filter. Click on Select All. This will deselect all the choices because they are all selected by default and clicking will deselect them. Click on the month you want and you will be presented with that month. Select the Pivot Table by clicking in the table then Ctrl + * to select the Pivot Table. On the Page Layout tab, click on Print Area, Set Print Area. Now you are ready to print. The print will likely take 2 to 3 pages as it will be about 120 rows long.

[anon125]

thanks
but i need each month on one page each.
oh well - crude copy and paste it is.
thanks again

[FDibbins]

What you want IS possible, you just need to put your data in the correct format. Jun (L2), is not a date to excel, it is just 3 letters, nor would excel know what Dec is, or any such text. To excel a date (real date) i simply a number that represents how many days have passed since 1/1/1900, so 6/1/1400 is actually 41791. Form that it should be clear than searching for "Jun" will never find a match with 41791.

Soooo how do we overcome this?

1 simple way would be to use filters

In the table you already have in E:H, 1st add a new row for headings. Then change the formula in E2 (weas E1), to =--LEFT(A2,10)...this will give you a real date, instead of text that just looks like a date
Then select Data/Filter/Auto-filter
Uncheck ALL, and check the month you want (June), click OK. All rows other than June are now hidden

If you really want to use the formula/vlookup route, then try this (all cell refs assume that you inserted the row for the headings)...

1st, I changed Jun to 6/1/14 and your vlookup worked in L4.
Now, I would not bother with using vlookup to return the dates, based on what I explained above, you can just use =L2 for the 1st 4 entries, and then in L8 =L4+1, copied down
2nd, vlookup() will only return the 1st match it finds, and then stop looking, but you need 4 returns per day. To do this, you will need to give each data-point per day a unique ID. To do this, copy this to I2 and copy down...
=E2&COUNTIF($E$2:E2,E2)

Now, in M4, copied down and across, use this...
=INDEX(F$2:F$1060,MATCH($L4&MOD(ROW(),4)+1,$I$2:$I$1060,0))

(Note, you only have 3 entries in June 5)

See the attached as well

[anon125]

thanks for your efforts - as a non expert it may take me a while to understand it!
so will i be able to put Jun in a cell and the software will look up the June tides and put them in three columns so it fits on one page?
same for the other months.