vlookup does not return expected results only #N/A

[captmorgan]

spreadsheet.png

I have attached an image of the spreadsheet to which I will be referring as the workbook is HUGE and copying the worksheet has so many links that it will not function by itself.

The columns to which I will refer are:

(B) Date - (C) % Guide - (L) Daily Total

"(B) Date" is derived from a formula which calculates and then displays the day of the month based upon whatever month it is such that it only displays the actual days for that month: 28/29, 30 or 31 days. The formula is: =DATE(YEAR(NOW()),$O$6,1) in the cell for the first day of the month and a relative reference of: =IF(DAY(IF(B6="",0,B6)+1)>DAY(B$6),B6+1,"") in the rest of the cells below for an obvious maximum of 30 additional cells.

"(C) % Guide" is derived by taking the COUNT of entries in column "B" from the first day of the month down to the particular row being calculated then dividing that by the maximum number of days in the displayed month. =COUNT(B6)/O42 in the first cell and =COUNT(B6:B7)/$O$42 on down to the last cell of the month. This generates a percentage of the month used up for each day of the month. The "O" column referred to in each of the about expressions is a figure representing the maximum number of days in the particular month being displayed.

The row to which I will refer are:

(42) % Achieved

This row/column is the far right bottom cell which, in the image, reads "20.1%" This figure currently represents the percentage of the production achieved based upon the figures entered above it.

Here's what I need:

I want to place the value of the percentage of the month which has passed in the cell to the right of the lower right cell noted above - "20.1%" - for comparison purposes then I would establish a conditional format to bring attention to it in some way. I have tried to use vlookup and other functions associated to its use to return the value associated with the entry in the % Guide column based upon it being the row corresponding with the last entry in the Daily Total column

In this case, the last entry in the Daily Total column is 73.0 which is the row corresponding with the Date & % Guide columns entry of "13" and "43%" respectively.

I get #N/A or #VALUE error in all attempts.

A COUNT of the Daily Total column always returns "31", I assume because there are formulas in each cell that produce the daily totals; a COUNTIF >1 produces a correct count of the cells, however, you will notice that the Daily Total of the first day of the month is empty so guess what the count is - yep -"12".

And of course trying to use these routines with vlookup just fails.

Letting the boss just physically refer to the % Guide column and comparing that figure to the % Achieve figure is just WRONG. Excel can do better than that - it is just that "I" can't do it.

HELP ! (If you need the workbook I could send it if you can explain how to get it to you individually)

Thanks in advance for any help you all can provide.

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)