Free Microsoft Excel 2013 Quick Reference

Using vlookup and date function Results

Hello,

I'm looking to make a dashboard of sorts. I have several workbooks that use COUNTA, COUNTIF, and TODAY functions to pull data from the sheets in the workbook into one summary sheet.

I now want to pull data from those separate summary sheets into one master/dashboard type sheet.

The main thing I need is a way to pull data onto this dashboard sheet comparing a target date vs. today's date, and then pull in all of the data that exists on the same row if that target date is greater than or equal to today's date.

I've tried VLOOKUP, but haven't been able to get it to return the entire row's data, and I didn't get too far with IF statements either.

Any help you can provide pointing me in the right direction is much appreciated.

Thanks in advance!

EDIT : (Dec 1, 2005)

hi all expert here.. I tried the VLOOKUP and also the OFFSET / MATCH combo that I found on the Net ... .. I still can't do what I want...

ok .. for example.. I am making a loan repayment excel.

col A1..A1000 ... I will have all the date in ascending order
col B1..B1000 .. it will contain my remaining balane ... since I am repaying .. it will be in a descending order...

and the B column changes as my repayment varies..

So.. basically I want to have a function .. that will go to the B column ... .. find out when B column becomes 0 or becomes negative and then return me the corresponding "date" in col A ..

So.. basical I want my excel to tell me when I will be debt-free

and none of the LOOKUP functions will work because
(1) there might not be an exact "0"
(2) the "remaining balance" column B is in descending order while VLOOKUP req. column in ascending order..

So... any expert here able to give me a hand ? ... maybe this is beyond sheet function ..but then how about some VB code ?

Thanks a million

BH

====================================================
(Original Post)

Hi all, I have some excel question and hope all the expert here can help..

last time.. the SUMPRODUCT really helped me a lot and I am still trying to really understand how it works..

anyways.... .. here is my problem...

Col A is a list of date in ascending order, Col B is an numberic value.

Say in cell C1 , I will enter a random date.. and how can I make Excel to search through Col A and find the closest date to what I have entered in C1 and return the corresponding Col B value ?

Well. not really closest... as long as Col A is a date that is less then and yet the closest .

imagine in Col A is the date and Col B is the histoical account balance ...

now.. in C1, I will entered a date and I want Excel to automatically tell me ... as of that date... how much balance I used to have ...

hope I am clear... and TIA for all your help

BH

For a while I have a problem using Vlookup
Sometimes when I want to compare two different columns sent to me by two different sources the Vlookup come back with N/A although I know and see the date is matching.

I know its a formatting problem but I can not fix it
Retyping solve it but its crazy
Also when I copy only the exact phrase from the function row its works.

I am using office 2003

I attached an example where I try to compare between column A to B

Good Afternoon all

I am having some trouble with a formula.

On sheet 1, cell B3 contains a date, cell D3 contains the word "day", or "night", cell B5 contains the formula below:

=IF(D3="DAY",VLOOKUP(B3,Sheet2!A5:C7,2,0),0)&IF(D3="NIGHT",VLOOKUP(B3,Sheet2!A5:C7,3,0),0)

If D3 contains "Day", the answer ends with '0',
If D3 contains "night" the answer starts with '0'

I know why, it is because I have put & into the formula.

Here is my problem, either I am using the incorrect functions to begin with, and if they are correct, I dont know how to nest them together correctly.

Any advice will be great.

Thanks

mdot218

Hi there,

I have produced a spreadsheet so users have a record of the jobs they do in a particular month.
To simplify.
Lets say I have a table of 3 columns. In columnA I have the job number i.e. 1,2,3 and in Columns B and C I have the start and finish times for that job, respectively. These times are fixed and I have named this range of cells to be used with the lookup function.

Now on a seperate worksheet I have a colA of dates for the current month and in colB the user is invited to enter the corresponding job they did on that particular day. The user enters the job number by selecting the job from a data validation list. Depending on what job they select, Col
C shows the start time and ColD the Finish time, by using the vlookup function to return the corresponding colums from the initial table created.

Now here's my problem. In this example we have 3 jobs with fixed start/finish times in a lookup table. Now lets say I introduce a fourth job. This has no fixed start/finsih time but is also included in the lookup table. What code can I use in the cells for the start and finish times for job 4 so that when 4 is selected from the data validation list the user is prompted to enter a start and finish time which then appears in ColC and D in our second sheet.

Many thanks in advance

Hey all,

I am new to excel and this forum, so sorry for the clumsiness.

So, I am trying to use Vlookup to 'grab' a date from one sheet and place it in another. It is important that if the value cannot be found then the cell is left blank (no #N/A).

I built the following and it works well:

IF(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE))

Next, I want the Vlookup to deal with three IF functions. So, if the first cell is blank it checks the previous, and if that cell is blank then it checks the previous and finally if that cell is blank does not return any value (including #N/A).

I tried this:

=IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,13,FALSE)),IF(ISBLANK(VLOOKUP(B17,Sheet1!A32:M94,12,FALSE)),I F(ISERROR(VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),"",VLOOKUP(B17,Sheet1!A32:M94,11,FALSE)),VLOOKUP(B17 ,Sheet1!A32:M94,12,FALSE)),VLOOKUP(B17,Sheet1!A32:M94,13,FALSE))

This does everything all the way to if the last cell is blank it places #N/A instead of leaving the target cell blank.

I have attached my sheets, if that helps.

Any thoughts?

If I need to clarify my question just let me know.

Thanks!

i have tried: =VLOOKUP(TODAY()-7,A2:M2000,3) to return data that corresponds to a date 1 week ago. Unfortunatley, it does not work because my dates in the array are in descending order. The order cannot be changed... is there another function I can use to return the value I am looking for?

Hello,

I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.

A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15

I have another table (table2) in another sheet in the same workbook have a material and date.

A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008

My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.

I have tried using vlookup but it only works for the first match and doesn't check for other values

below is the function that i tried

=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")

Can anyone please help me with this problem.

Hi,

I'm trying to do a Vlookup type function where I would return a speed value
from one table to another, but I can't figure out how to make it work. the
source data table looks like this:

SubDv From To MPH
Squamish 0 3.5 15
Squamish 3.5 10.9 20
Squamish 10.9 42 25

and the receiving table looks like this:

Date Time MP ACT SPD TT SPD
06-Apr-2005 06:47:41 2.15 0
06-Apr-2005 09:58:09 2.15 0
06-Apr-2005 09:58:24 2.15 1
06-Apr-2005 09:58:45 2.16 2

In the column "TT SPD", I want Excel to lookup the value in MP and if it's
between the "From" and "To" columns in the source table, to return the "MPH"
value to "TTSPD". I can't use Vlookup unless I breakout the short table to a
4000 line table...I don't know how to make an array to do this...help?

Thanks in advance,

Justin

Thank you so much to all for the help, but How would I add the rest of the formula to calculate past the "end of month" period for 2 months 3 months, 4-6 months, 7-12 months, 2 yrs, >2yrs? my post below explains the situation a bit further. anotehr problem arises, that the number of days in the month is constantly changing, how can that be accounted for and still categorize them as the 2M, 3M, 4-6M, etc.

thank you once again and I greatly appreciate everyones help.

SK

Quote:
Originally Posted by sk81222
I need to create a recognition for a column. for example. i have a list of dates starting from May 1, 2006 all the way to December 3, 2010.

and the dates repeat as well.

example:

05/01/06
05/01/06
05/01/06
05/01/06
05/02/06
05/02/06
05/02/06

what I need is for the column beside the dates to show me a categorization of different time periods:

for example. if 0 - 1 days have passed from the original date of May 1, 2006, then i need it to say O/N , if 2-7 days have passed since May 1, 2006, then i need it to say "2-7 days". if 8-15 days have passed, i need it to say "8-15 days", the tricky part is that I need to see whether dates fall in between the 16th day of the month and the Last day of the month, so for example, in May, between May 16 and May 31, then i need it to say "16 - End of Month". the problem is that months have different amounts of days and i cant figure out how to capture this part.

after that i categorize the sections as Month 2 if the date falls in between June 1 June 30, then son on until for 3 months, 4-6 months, 7-12 months, >1 yr but less than 2 years, and >2 yrs.

i cant use the IF function becuase it only has 7 entries i can use.

Can anyone help me figure this, would i use vlookup, if and possibly other functions combined? or maybe a macro?

I would appreciate all the help I get.

best regards.

SK

I am trying to extract data from a spreadsheet using a search function that
has two search criteria. For example, I have a worksheet filled with
employee performance data and need to extract data to a table based employee
name AND date. Something similar to VLOOKUP but with two filters instead of
one.

Assume 2 columns of data: due date and project name, sorted by due date
(multiple projects/due date possible). I need to fill a calendar (1
mon/sheet) with the project names on the date they're due. I can use VLOOKUP
to fill in the first cell for the date, but how do I get the second project
name on the second cell for that date? VLOOKUP, as far as I can see will
only deliver the first matching value when I specify FALSE as the last
argument.

Any ideas for using another function? It strikes me that what I need is
something similar to the "next record" field in Word's mail merge. VB
programming is not an option -- not currently a talent of mine. Thx.
--
IdeaRat

Hi, this is quite a random request, but I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date.

I now need to use that returned value in the CORREL function. That is, I would like it to look something like:

=CORREL($E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2))

Does this make sense? When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?

Thanks so much for your help and please let me know if I can clarify anything!

Michael

So then using comments to mark accessed cells (as this is the only option
with functions since they are not able to modifying cell formats in other
ways)

Function FindOldNominal(NomCode, definedRange)

FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5, False)

'(now attempt to mark the accessed cell - the bit not yet working!!!!)

Dim rng As Range
Dim noOfRows As Integer

noOfRows = Match(NomCode, definedRange, 0)
rng = Offset(A1, noOfRows, 0, 1, 1)
rng.AddComment ("accesses")

EndFunction

I realise the above is probably incorrect syntax, but have only just started
to learn macro and excel functions (not even sure functions are found in
what classes etc).

Please could you ammend the above so it works. Hopefully the gist of what
i'm trying is clearer enough.
Many many thanks in anticipation
James

PREVIOUS POST

""Gary replied..............

A function can deposit comments in cells:

Function demo(r As Range) As Integer
demo = 1
If r.Comment Is Nothing Then
Else
r.Comment.Delete
End If
r.AddComment Text:="marked"
End Function

This dumb little macro just returns 1, but it marks its reference with a
comment

=demo(A1) results in A1 getting a comment. So even if you can't color A1,
you can mark it.
--
Gary's Student

"James Cornthwaite" wrote:

> o right, never straight forwards is it.
>
> Is there any other way around the problem, to achieve the same
> goal.??????????
>
> Thanks
> James
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> >A function called from a worksheet cell can only return a value to the
> >calling cell. It cannot change the value or format of any cell.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in message
> > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> >> Ideally i would like to color the contents of the cell accessed by the
> >> call of my function "findnewnominal(...)" The reason for this is the
> >> function is called many times on a range table range and I would like
> >> to
> >> know at a glance which rows in the table hadn't yet been accessed by a
> >> calls of the function.
> >>
> >> My macro to date (which works fine is)
> >>
> >>
> >> Function FindOldNominal(NomCode, definedRange)
> >>
> >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> false)
> >> EndFunction
> >>
> >>
> >>
> >>
> >>
> >> but if i want to color the cell accessed I imagine i have to use
> >> something like the following? (my attempt of something pseudocode)
> >>
> >>
> >> Function FindOldNominal(NomCode, definedRange)
> >>
> >> Activecell = reference(Worksheetfunction.VLookup(NomCode, definedRange,
> >> 5, false))
> >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> >>
> >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> false)
> >> EndFunction
> >>
> >>
> >> I appreciate the above is probably non sensical but know little about
> >> excel functions and just wanted to make as clear as possible what i
> >> wanted to do.
> >> Any help would be greatly appreciated.
> >>
> >> Thanks
> >> James
> >>
> >>
> >
> >
>
>
>

Hi

I need help in the following:

I have another summary report which I need to tally my ID, Item and COM date (IN RED) to the ones in Mthly Report.xls. If the referencing data (all three tabs, Jan, Feb and Mar) for the three columns (RED) in Mthly Report.xls matches all the conditions in the ones in Summary.xls, we will pull out CFM_Date and ETA_Date (IN BLUE) and display in the summary report columns. If there is no matching conditions, the columns will be blank.

I am trying to use a nested IF function with vlookup and have tried for some time now. But it still shows me error. Please help me on this.

I am using excel 2002.

Thanks

Tom Screw

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. If there is anything you can suggest to speed up the processing I would greatly appreciate it. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long.

Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

Thanks in advance for your help.

I realized the vlookups were not the problem it was the VBA code.

Here is the concatif funtion

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As
Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like COUNTIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function


I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is a
holiday then we have to add a Plus/Minus modifier in this column which tells
the formula to add or subtract days for cutoffs on delivery or receiving. Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on this
page and returns the Impact for that day of the year. However, when I run my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update it
to work better. We have several people who use versions of this spreadsheet
and make mistakes on it because they do not understand the formulas, so I am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Function doLookup(data As String)
On Error GoTo ProcessError
If LCase(data) = "omit" Or data = "" Then
doLookup = 0
Exit Function
End If
Dim data1 As Date
data1 = data
Dim myRange As Range
Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")

MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2, False))
Exit Function

ProcessError:
MsgBox (Err.Description)
Resume
End Function


Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the expected 4 and
all 5 entries are the details of the last entry for Chub in the lookup list.

Date Place lbs ozs drms
Kilos
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
#N/A #N/A #N/A #N/A #N/A #N/A

I have obviously got the formula wrong or misinterpreted the way this
function works. Is there any way to get this to work? If so could you also
tell me how I can return a blank cell instead of #N/A when the result is not
TRUE?

Any help would be gratefully appreciated.

--
John
Delete extra @ to reply

Hello everyone. I have the following snippet of code that I use to assign a cell in a worksheet a variable from VBA:

Sub Update_Formulas_Data()

    Dim sPath1      As String
    Dim wb1         As Workbook
    Const sFileInp1 As String = "Formulas.xlsm"
    
    sPath1 = fPath & fDate & "_157Support_Summaries"
    
        Set wb1 = Workbooks.Open(sPath1 & sFileInp1)
        Range("AV1").Value = fPriorDate
        Range("AV1").NumberFormat = "dd-mmm-yyyy"
     
End Sub
It works. I need help creating a vlookup formula which will use that cell refernce. First I have to concatenate the cell reference with the suffix "_CDS", and I must do so while mainting the dating format, dd-mmm-yyyy. Thats is where I am currently stuck. After that is done, I believe I should be using the Indirect worksheet function, but I am not sure how that is done. Any help is greatly appreciated.

Hi, thanks for reading this. I have a 2 page workbook with invoice data
entry on the first page and i am looking to pull off totals onto the 2nd
page based on month number in the date column. My date column (B) contains
formatted date dd/mm/yy and for example column F contains the invoice sub
total in currency. My invoice dates are not in date order.
I want to have a row of fields on Sheet2 which have monthly totals in summed
from the data in sheet1, for example sum all the invoice totals of the whole
sheet if those invoices have the date month of 04.

I think I want to use VLOOKUP, maybe with Right or MID to pick the month out
of the date cell. What i am not sure about is how to put the function into a
range and is it all wrapped in a SUMIF?

Any help much appreciated, thank you.
Chris


No luck finding an answer? You could always try Google.