Free Microsoft Excel 2013 Quick Reference

- Vlookup against 2 criteria
- VLookup and Hlookup combined using separate workbooks
- Require SUMIF conditions,
- Need Excel Function to FIND Text - Help!
- VLOOKUP or IF or BOTH
- Vlookup w/Date Function
- ISNA vlookup return matched result
- Multiple lookup function fails when called as a UDF
- Custom formatting using either Macro or VB script
- Vlookup troubles...
- Vlookup in multiple arrays
- Deriving letters from numbers that fall within certain ranges.
- VLOOKUP choose between rows
- Vlookup within a Hyperlink
- VLOOKUp (Within the Cells)
- VLookups data updating before WorkBook_Open event.
- Tricky vlookup/if function/coding help!!
- Array Function with Multiple, Variable, Dependent Criteria, Write Destination
- Picking out data and combining it from 2 columns
- Extact next word after FIND of another word within string

I've been asked to see if this is possible, and Vlookup is the only way I can think of off the top of my head.

What I need to do is this.

I have 2 tables. In table 1, I have incident dates, and builders assigned, on 1 line.

In table 2, I have an XY table, with Builders, Reporting periods, and ranks. E.g.

-----------Period1--Period2

Builder 1--Gold----Silver---

What I need to do is lookup the builder and incident date in table 1, and match it to the rank for the period that date corresponds to.

For example. On 12/Jan/2008, Builder 1 was assigned to a job. 12/Jan/2008 is within period 2 (Nov 07-Jan 08), so the function should return "Silver"

I'm pretty sure this is going to be a bit more complex than a simple VLookup, potentially combining functions (Maybe matching a Vlookup and a HLookup?) but I'm not sure where to start. Your assistance and expertise is greatly appreciated.

-Bob

Edit: After some experimenting, A Vlookup combined with a Match is working fine, I just need to figure out how to format my dates. For example, how do I match 01/01/08 with "01/11/2007-31/01/2008"?

What i am hoping is to find a formula/function that would be able to take the client name, do a vlookup within another workbook, then do a hlookup to find the appropriate assignment and grade.

I noticed the other thread just a few down describing how to do such an act within the same workbook/worksheets, but is it possible to branch out to another workbook?

any help will be much appreciated, and if anymore imformation that may be needed do not hesitate to ask! TIA!

btw: i have to say that after lurking for a lil bit, I have realized that excelforum.com is quite the reference tool! Already have it bookmarked!

Is this possible?

Require SUMIF conditions,

I have a worksheet(2 DC-Process Deviations) where all the input data populated by user. Based on these input data, I'm doing some calculation in another worksheet(1 Process Deviations Detail).

Like what I need here is in sheet(1 Process Deviations Detail) is,

calculations is based on phase Reviewed 3(cell D7:D56) from the data populated within sheet(2 DC-Process Deviations) ,

Row 7 and 13, Total Possible Compliant Items = 510 + 198 + 72 = 780

Row 7 and 13, Process Deviations = 24 + 23 + 12 = 59

Row 7 and 13, % NonCompliance = 7.6%

Rows 24 and 59, Total Possible Compliant Items = 20 + 10 + 20 = 50

Rows 24 and 59, Process Deviations = 5 + 1 + 3 = 9

Rows 24 and 59, % NonCompliance = 18.0%

Rows 25 and 72, Total Possible Compliant Items = 22 + 34 + 20 = 76

Rows 25 and 72, Process Deviations = 4 + 5 + 6 = 15

Rows 25 and 72, % NonCompliance = 19.7%

Similar addition of values should be done for all reviews within the same phase for the other process areas.

Note:- Right now, it is taking only the first value for phase reviewed 3, available within sheet(2 DC-Process Deviations) for phase Reviewed 3 in column D,

for example, Formula in cell "D59"

=IF(ISERROR(VLOOKUP($A59,DC_Process_Deviations_Table,Controls!D14,FALSE)),"",VLOOKUP($A59,DC_Process _Deviations_Table,Controls!D14,FALSE))

Likewise Formula in cell E59,

=IF(ISERROR(VLOOKUP($A59,DC_Process_Deviations_Table,Controls!E14,FALSE)),"",VLOOKUP($A59,DC_Process _Deviations_Table,Controls!E14,FALSE))

I think instead of VLOOKUP function in cell D57 to E287 , it requires SUMIF function for what I need.

Please help, It's urgent, Please.......

You can refer attachments for help.

Thanks,

Raushan

I run an online store with a huge selection of inventory. My inventory is downloaded from my distributor into an excel csv file. I like to go in a "pretty up" the entries to make it presentable to my shoppers. The trick is that there is about 10,000 products with descriptions and the works in this sheet.

What I'm wanting to be able to do is this. I want Excel to search a column. Within this column are the descriptions of every product. I want this search to find a particular word within these strings of text. If the word is not found, then the result would be nothing. If the word IS found, then I want Excel to do a vlookup on another column to match that word and return a string of text which would be added to the description.

Example:

I have 3000 knives within a spreadsheet. Within the descriptions of these knives are the type of steel used in the knife. I want Excel to find the name of the steel (i.e. "440C"). When and if found, I want it to take that steel name and go to another section or sheet and do a VLOOKUP (or whatever function is needed) and match that steel type. Upon doing this, Excel would return the description of the steel which is listed in this section or page and add it to the original description of the knife.

Hopefully this makes sense. If anyone could help here, I would greatly appreaciate it.

Cole

I've been puzzling over this one for a while so I'd appreciate some

fresh eyes taking a look (and hopefully saying "that's so easy" and

proposing something so simple I slap myself in the forehead...*wry

grin*).

In A10:A17 I have a list of sample IDs for two sorts of water (muddy vs

clean). In B10:B17 I have a list of the samples that they were mixed

with.

1 X

2 5

3 4

4 3

5 2

6 17

7 8

8 7

As you can see, sample 5 mixed with sample 2, sample 1 didn't mix with

any samples, sample 6 mixed with sample 17 (this is just a snippet of

the values), etc. Let's say that we know sample 2 has a particular

feature (eg: it's muddy water). Because it mixed with sample 5 (clean

water), sample 5 is now the same as sample 2 (muddy water).

Since I know sample 5's status to begin with, a simple IF statement

within the table is fine to show sample 5 as "M" in column C. What I

would like to do is to somehow get the value from column B (ie: 2) and

then make sample 2 also "M" status. (Note: If two clear water samples

mix (eg: samples 7 and 8) they should remain clear and stay as "C"

status).

I've tried this in multiple columns and within the same column with no

success using VLOOKUP and IF combinations. I'm finding the problem

seems to be that Excel runs through the formula to determine the status

of sample 2 but then doesn't re-evaluate the status after sample 5 is

found to be of a different status. Can someone shed some light on

other functions I might try or if I'm just making some stupid mistake

and you can get those functions I mentioned to work without a problem?

Thanks in advance!

range_lookup=FALSE so I am expecting an exact match or an error will result

(#NA).

Within the DATE function, I determine the year and month and then I supply a

day value between 0-31. Everything works as expected if the day value is

between 1-31. But if the day value is 0, then the VLOOKUP returns result for

the last day of the previous month instead of an error. Here is the function:

VLOOKUP(DATE(YEAR(R31C2),MONTH(R31C2),DAY(R[-1]C)),

Scheduled_Installation_Date,2,FALSE)

=IF(ISNA(VLOOKUP(A13,'FAX CONTROL INFO'!$L$2:$L$1061,1,FALSE)),"'FAX CONTROL INFO'!N13","Yes")

For example:

sheet1

A

==|===|

1 | 10

2 | 11

3 | 12

sheet2

A B

==|===|====|

1 | 10 john

2 | 11 fred

3 | 12 bill

My look up would check to see if sheet1, A1 exists within range sheet2, A1:A3 and return B1 as it is a true match with sheet2, A1 - hope this makes sense. As a note, I will have multiple matches but only need to return one result as all matches would be the same.

If anyone has done this sort of thing before and can help me out or point me to another resources it would be much appreciated â€“ thanks for your time.

I have a fairly straightforward function to concatenate the results from (effectively) a VLOOKUP on a range where there are multiple matches ...

Function MultiVLookup(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String Dim rngMatchValue As Range Dim sFirstAddress As String Dim sTmpReturn As String sTmpReturn = "" With rngLookUpArea Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not rngMatchValue Is Nothing Then sFirstAddress = rngMatchValue.Address Do sTmpReturn = sTmpReturn & rngMatchValue.Offset(0, lOffset).Value & sDelimiter Set rngMatchValue = .FindNext(rngMatchValue) Loop Until rngMatchValue.Address = sFirstAddress End If End With If Len(sTmpReturn) > 0 And Len(sDelimiter) > 0 Then sTmpReturn = Left(sTmpReturn, Len(sTmpReturn) - Len(sDelimiter)) End If MultiVLookup = sTmpReturn End FunctionIf I call this from a VBA subroutine it works fine, and produces the expected results:

Sub TestCall() MsgBox MultiVLookup("A", ThisWorkbook.Sheets(1).Range("A1:A3"), 1) End SubIf, however, I call it as a UDF it fails. A quick bit of debugging shows that this is because the .FindNext method returns an empty range, which shouldn't be possible, as there are both more matches within the range and, even if there weren't, there must have been at least one match, so it should loop back to that.

Things I've tried:

1. Naming the range explicitly, rather than using a With statement - no difference.

2. Defining another range within the function and setting it to the search range before the first search - no difference.

However, the brainwave I had half-way through typing this post was to replace the .FindNext statement with:

And that does work as a UDF.

So, I guess my question not isn't so much "How do I make this work" as it is "Why the hecky-thump didn't it work with .FindNext". Anybody care to enlighten me?

Thanks.

Ok, what I am trying to do is this. I have a report at that I am trying to extract information from that can be copied and pasted into another which already has a VLOOKUP function implemented to reference data within a certain format.

The report that has the source information is in a different format going down columns with multiple duplicate entries in various cells down the columns.

I have a macro that seperates out the data from the report that I ened but I am having trouble getting the data to format the way I need automatically. I have tried using a Copy>Paste Special> Transpose but it causes the data to not format correctly. Transpose is the function that I believe I need because the format goes from left to right instead of down the page on the report that needs the ference data.

I believe I could do this in VB by setting up a script that would say something a long the lines of this psuedo code:

Index Column (select column)

If Value, transpose to cell to right

Else, leave blank

Any ideas on how I could accomplish this?

This would be sheet one:

BIGC4 MONTCTY2 55BIG_CK MONTCT

And let's say that there is an identical arroay of cells in sheet two.

Then in a fifth column in sheet 2 there is a value of either "M" or "S" or it is blank. I want the function to search for the array from sheet one in sheet two, if it is not there or if column 5 is blank, then return nothing, if "M" then return "M" or "False" and if "S" then return "True". Is there any efficient way to do this?

different months. The first column of these worksheets contains an

identifying number. I named the first column of these sheets the month name

and the full lookup array the month with the number 1 on the end. I used the

following array formula to lookup the value in column three of the different

lookup arrays for a cell D2 containing an identifiable number.

{=IF(OR(D2=JANUARY),VLOOKUP(D2, JANUARY1,3, FALSE), IF(OR(D2=FEBRUARY),

VLOOKUP(D2, FEBRUARY1,3,FALSE), IF(OR(D2=MARCH),VLOOKUP(D2, MARCH1,3,FALSE),

"NOT VALID")))}

I would like to have this formula hold true for the entire year, but I can

only nest 7 if statements. I think I need to write a select case custom

function, but I do not know the syntax to compare the function value to the

different months. Here is a code example of what I want to do.

FUNCTION SPECLOOKUP(ID#)

SELECT CASE

CASE ID# IS WITHIN JANUARY

SPECLOOKUP = WORKSHEETFUNCTION.VLOOKUP(ID#,JANUARY1,3,FALSE)

AND SO ON.

END SELECT

END FUNCTION

Can someone tell me how to write the cases correctly?

--

Thanks

R. Patterson

I'm having an issue doing something, and while I have seen similar questions, I haven't found one with specifically the same issue.

I have 8 number ranges:

A = .01-5.00

B = 5.01-25.00

C = 25.01-50.00

D = 50.01-100.00

E = 100.01-500.00

F = 500.01-1000.00

G = >1000.00

WHat I am trying to set up is assiging a letter to a cell when the value of the referenced cell contains a number that falls into one of these ranges. Issue is, these ranges may change, so I have set them up with each in their own cell.

I was then using the IF and AND functions to dictate that if the course cell's value was greater than, say C2 and less than cell D2, then input letter in cell E2.

This worked for two ranges, but after that, i can't get the formulas to work, and I assume it is because of the limit on logic functions.

I have seen to VLOOKUP, but I have not had any sucess setting that up yet.

Is VLOOKUP capable of doing what I am attempting? I know I could hardcode many of these values in and then change later, but I am trying to set up a system that is easily adaptable by another use should conditions change.

Any thoughts?

For example, for LCP017, the cell on the MAIN tab K21, should have the value "5/6/2011" because it's greater than the alternate value for the same well, for the same quarter, on the DATA tab ("Pending as of 4/8/2011"). I have a formula that can decipher the date within the text and compare to another cell:

=IF(IF(ISNUMBER(A1),A1,DATEVALUE(RIGHT(A1,LEN(A1)-14)))>IF(ISNUMBER(A2),A2,DATEVALUE(RIGHT(A2,LEN(A2)-14))),A1,A2)

But I don't know how to apply this with a VLOOKUP because vlookup only allows you to choose one. I tried plugging in Vlookup false (everywhere A1 occurs above) and Vlookup true, (everywhere A2 occurs above) but it evaluates to an error.

In a nutshell if I have PENDING AS OF 4/8/2011 for quarter 2 on the DATA tab and the next record for the same well has 5/6/2011 for the quarterthe DATA tab, I need the formula to take 5/6/2011 because the date is more recent.

http://www.mediafire.com/?jq66auiawfws6gt

Unfortunately I am not an excel wizard so need help with something that may be very simple.

I am trying to perform a vlookup within a hyperlink function.

I have information in a workbook (called "Test.xls") on two sheets. On a sheet labelled "BOM" I have 10 lines of information.

In another sheet labelled "Sales Orders" I have one line that I wish to lookup within the "BOM" sheet, with use of the hyperlink function so it takes me straight to the line I require.

At present my formula reads:

=HYPERLINK("[Test.xls]VLOOKUP(C3,BOM!D2:D11,1,0)","NEXT >>>")

Unfortunately my reference is not valid. Can this function be performed?

I hope this explanation makes sense, if not I'll have to try again.

Thank you in advance for any assistance.

1) Now, Is it possible (if yes, please mention the function) to VLOOKUP a text within the cell to another cell ?(eg. A! contains "The Quick Brown Fox" and C1 contains "Brown" C2 contains "cont" and C3 contains "another"; Then the Vlookup should put the result against C1 and C2)

2) Is it possible to VLOOKUP a textword within the cell to a textword of another Cell. If yes, please mention the formulae.

within this sheet I have a function which copies the lastest version of a

file down, which I actually want the VLOOKUPs to look at.

The code I have is...

Private Sub Workbook_Open()

On Error Resume Next

Dim fsoFileObject As Object

Dim folFolder As Object

Dim strFileLocation As String

Dim strSC3Location As String

strFileLocation = "C:SC3"

strSC3Location = "ias_01StatisticsAffinityAffinity 2004Affinity Costs

2004Backup2004.xls"

Set fsoFileObject = CreateObject("Scripting.FileSystemObject")

If Not fsoFileObject.FolderExists(strFileLocation) Then

Set folFolder = fsoFileObject.CreateFolder(strFileLocation)

End If

Application.DisplayAlerts = False

If fsoFileObject.FileExists(strFileLocation & "Backup2004.xls") Then

Set folFolder = fsoFileObject.DeleteFile(strFileLocation &

"Backup2004.xls")

End If

Application.DisplayAlerts = True

fsoFileObject.CopyFile strSC3Location, strFileLocation & "Backup2004.xls",

False

End Sub

The issue I'm getting is that the sheet is asking if I wish to update the

links to get the fresh data before the WorkBook_Open event even fires off!

Any ideas how I could possibly slow this down at all, or how I can re-order

the data updates until WorkBook_Open is complete??

TIA.

Each team has training on every weekday, except where there is a match (indicated by Match column, C). So if match day is Friday, training will be on Monday, Tuesday, Wednesday and Thursday. Sometimes training is twice on the same day (two entries within spreadsheet for training day).

I need a formula or some coding that tells me if a team is not training on all other weekdays other than the match day. Whether it is one day missed or more, just a ‘false’ will suffice. For example if match day is Friday, and the team only has training on Monday, Tuesday and Wednesday I want a ‘false’ to be shown. If the team is training once (or more) on each day, other than the match day, I want ‘true’ to be shown.

I thought this would be fairly simple, however, have had no luck with vlookups and if functions. Any help would be greatly appreciated.

Sample spreadsheet attached.

Direct POM Number Code Agent Order No (columns continue)

1 040 129XY1 2000 90023A

1 040 129XY1 2000 90023A

_ 052 856G02 2000 90028A

_ 040 129XY1 2000 90028A

_ 052 856G02 2000 90028A

The actual match is dependent on whether or not all POM cells for any given PO match each other. I don't want any of the records flagged if any single POM location for that order differs.

The source file varies daily in the number of records, depending on how many orders are pending. (Today's file was about 7000 lines). The lines on an order will vary, depending on what we have in backlog. POM locations vary within a range of about 20 different locations. I'm trying to write a function that can simply be copied down as needed.

I've tried nesting within a plain IF statement as follows: {=IF(SUM(($B$23:$B$5=B4)*($E$2:$E$23=C4)),1,0)}, but it returns a "1" every time - because, of course, cells always match themselves. I started to try a nested IF with a VLOOKUP, but that doesn't seem feasible. Since the data is dynamic, an INDEX (or other static location) call doesn't seem like it would work, either.

Obviously, I'm missing something, but I don't know what.

I am running Excel 2003 and have attached a small example file.

I have searched but been unable to find anything similar dealing with dependent variables. Please point me in the right direction if I missed it. Any help at all would be greatly appreciated.

as you can see in this picture:

http://img186.imageshack.us/img186/9...uestionxk0.jpg

i have 2 columns (actually more, but just for this example) and there classified in the right column as a,b, or c and on the left i have values for the individual properties within these building classes.

What i want to do is add up all of the properties in the a class, b class, and c class and put them in one small table on the right. I was thinking it would involve either or both of the IF THEN and VLOOKUP functions.

any help would be most appreciated!

I have a sheet with columns of data, 2 adjacent columns have data, example below:

Column A Column B Column C

Base Model Model Description

Avalon Toyota Avalon XLS Sedan

Avalon Toyota Avalon XL Sedan

Camry Toyota Camry Hybrid Coupe

Camry Toyota Camry XLE Wagon

What I'd like to do is a find, searching the text string in Column B, for the occurence of the contents in column A, and then return the NEXT word after the found word in column C. I suspect I many need to use a vba function, but lack the skills. Currently I'm using a MID an LEN find formula (below) in Column C, but it's limited to a character count, and I only want to return the next whole word.

=MID(B2,FIND(A2,B2)+0,5)

This returns the following:

Column A Column B Column C

Avalon Toyota Corp. Avalon XLS Sedan XLS

Avalon Toyota Avalon XL Sedan XL S

Camry Toyota Co. Camry Hybrid Coupe HYBR

Camry Toyota Motors Camry XE Wagon XE W

What I want to return is just the trim level: Hybrid; XLS, XL or XE for example. As you can see, when using the character count, it may also extract into the body style; such as the W in wagon, etc. I've tried variations using RIGHT also, and still seems to be limited by the character count.

Eventually, if it works, in column D I'll concatenate the Column A and D together to return Avalon XLS, etc.

I know there are ways to filter, sort, vlookup, etc, based on all the model types, but those solutions won't work, because the data I'm using isn't toyota cars; not trim levels, and I have a sheet which is 77,000 rows long, by 90 columns wide, and there are over 18,000 unique/distinct values in column B.

I've also considered converting text to columns, which fails because sometimes the 'trim' level is the second word, sometime it's the 10th word; but it ALWAYS follows the model (which is listed solo in column A).

Thanks,

Andrew

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