Free Microsoft Excel 2013 Quick Reference

- Conditional statements in VLOOKUP?
- How to make Excel detect the "closest" date to the one I entered and return an value?
- Vlookup N/A formatting Issue
- nested if statement using vlookup
- User Input with Vlookup
- Vlookup with nested if functions
- Lookup function and dates in descending order
- Vlookup with conditions to find multiple entries
- How do I lookup numbers with a MIN and MAX?
- recognizing dates past End of Month
- How do I search excel spreadsheets using multiple search criteria.
- Can VLOOKUP deliver the second occurence when target column entri.
- Using VLOOKUP return as row number in function
- Add comments via a function - please could you ammend my code/ pseudocode to work
- Using Nested IF and vlookup to extract data
- VLOOKUP performance issues
- VLOOKUP calendar dates
- New to using VLOOKUP Function
- Using indirect worksheet functions and vlookups
- Range :Vlookup query

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!

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

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

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

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

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 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.

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 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

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.

(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

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

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

> >>

> >>

> >

> >

>

>

>

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

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

(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

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

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 SubIt 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.

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