Free Microsoft Excel 2013 Quick Reference

Vlookup functions within if Results

Hi everybody,

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

I have finally hit a brick wall! I am trying to integrate two separate files, one a gradebook and the other a client file that will record grades given that are placed within the workbook.

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!

Hi Friends,

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

Hello folks,

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

All,

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!

I use VLOOKUP with DATE function as the lookup value. I set the
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)

I have created the following function to cross reference a cell holing a number with a range of cells holding numbers on another sheet, using false for identical match. My problem is that I need to return the value of an adjacent cell to that of the true matched result.

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

Morning all (it's morning in the UK, at least) - this is a problem I've come across in the past and worked around, but I wonder if one of the gurus here can tell me what the issue is.

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 Function
If 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 Sub
If, 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.

Hi,

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?

First of all, I'm not sure if vlookup is even the correct function, and I don't think that it is, which is why I'm posting in the programming section. Basically I've got one excel workbook with two sheets. In sheet one there are 4 columns with some of the rows identical to some in the second sheet. The trouble with vlookup is - according to this brief tutorial http://www.ozgrid.com/Excel/excel-vlookup-formula.htm - is that it cannot search for multiple cells within multiple cells. I'll give a short example:

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?

I am trying to lookup values from a work book that has the same worksheet for
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

Hello!

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?

I am in need of a specific formula for my spreadsheet. Ive been reading non stop on this since I ran into this roadblock, but can't seem to translate any formula to work with my spreadsheet. I need to perform a VLOOKUP type of function on the MAIN tab to match a well and compare data on the DATA tab.

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

Hello,

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.

I hope everybody is familiar with LOOKUP functions of Excel.

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.

Guys, I have a sheet which has VLOOKUPs onto a different workbook. Also,
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.

So I have a large excel related problem that I need sorting. The table shown is just a fraction of the data. Number of rows is over 4000.

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.

I have a spreadsheet showing orders and manufacturing locations. Each order is broken down to the line level and the point of manufacture is shown for each line. I need to write a formula which will (a) check the "Order No" column for all occurences of each order number, then (b) find the first "POM Number" record associated with that order number, (c) determine if all POM instances for that Order match the first instance and (d) if so, write a "1" in column "Direct," else leave it blank (or write a "0" if something must be written).

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.

I have a worksheet as you all know i've been asking a bunch of questions about.

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!

Hello,

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.