Variation On An "if" Function & "lookup"
Feb 17, 2010
I have created a workbook for keeping records of all maintenance to vehicles in our fleet.
I have a sheet called Vehicle maintenance input:
& then another 10 sheets with the vehicle callsign.
So
Sheet 1 :- Input all data sheet
Sheet 2 :- Y4
Sheet 3 :- Y7
& so on.
I need a formulae that will copy multiple info to the relevant sheets, I have found a way of copying multiple info, with the formulae below, but I can't get it to work if there is more than 1 sheet.
=IF(ISERROR(INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2)),"",INDEX($A$1:$B$24,SMALL(IF($A$1:$A$24=C$27,ROW($A$1:$A$24)),ROW(2:2)),2))
View 9 Replies
ADVERTISEMENT
Aug 6, 2009
I am trying to find a solution to a vlookup issue. I have attached to this post a sample excel file which will make it easier to understand. I have data in one table (Table 1) where the records consist of addresses with street field, city and zip. These records have to be flagged based on the street address (number and street name provided the city or zip is the same) with a flag called "Type"(as an example Type 1 or type 2, etc).
Than I have another table (Table 2 or the lookup table) that has unique adrresses with the "Type" field that needs to be brought over to Table 1. The issue that I run into is that addresses in Table 1 have variations such as 25 Main Street can be as 25 Main St., or 25 Main St, or 25 Main st., Apt 2, which all should be recognized as 25 Main Street. All we care is to flag 25 Main Street with it's appropriate flag from Table 2.
Data in Table 1 can be in the 100k or more records in some cases so any manual intervention would be very time consuming and not efficient. Now I've tried doing lookup only on the 3 or 4 left characters of this field but than you get thrown a curveball when the address is for example 25 Maitland Avenue, which will falsly flag them as 25 Main Street. Plus house numbers can be 25 Main, or 255 main 2554 Main or 2 Main, so it is not functional to use the left function nested within vlookup. This issue seems to be more of a logical validation, which I don't know how to approach. The best way to think about this is when you go print a priority label on the USPS website and they correctly validate your address eventhough you might have mistyped your address.
View 2 Replies
View Related
Aug 11, 2009
I have been working on a spreadsheet with 3 sheets and have done everything except the last function which I just can't work out. Data is entered on the first sheet in a list with an item number, description, dates etc and then finally, if they are a certain type of item, a variation, a 'variation number' in a column.
The next sheet is then called 'variations' and here starts my problem. I want to have a formula that picks up if a variation number has been entered on sheet one in the 'variation number' column and if so, add that item into this sheet, in order.
So to summarise, I need sheet 2 to search on sheet 1 in a specific column for numbers 1-30(ish) for a series of items. I then need sheet 2 to list each of these in order, copying over each item that goes with the number from sheet 1 in the same format.
View 5 Replies
View Related
Feb 21, 2014
I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","8),countif('Tuesday-No Shows'!D:D,"*" & A2 & "*"),countif('Wednesday-No Shows'!D:D,"*" & A2 & "*"),countif('Thursday-No Shows'!D:D,"*" & A2 & "*"),countif('Friday-No Shows'!D:D,"*" & A2 & "*")))
The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:
=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Tuesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Wednesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Thursday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Friday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*"))))
A2 designates the students first name and B2 designates their last name. If I eliminate the OR and resort to multiple COUNTIF's, it will generate twice the number of responses that I want, I fear.
Example.xlsx
View 6 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Mar 25, 2014
I think those are what I need but I can not get it to work.
What i would like is:
In column E rows 2-12 I will be entering one each of the items listed below in column e rows 14-23. As I enter an item in section (e2-e12) the corresponding entry in section (e14-e23) disappear leaving me with only the choices that have not yet been used.
View 6 Replies
View Related
Jan 6, 2004
Is it possible to use a vlookup to produce a number of results…
I have a list of surnames, and I want to use the vlookup to find all the Smiths in the list, obviously a basic vlookup only brings back the first Smith it finds….
=vlookup(A1, download,1,false) , where A1 is some sort of search box for users
or is there a simple VB solution?
View 9 Replies
View Related
Apr 18, 2008
Is it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
View 9 Replies
View Related
Sep 10, 2013
I have attached sample.xlsx...In this spread sheet, two sheet are there..
1. Invoice Level
2. Product Level.
Both sheets Billed amount, discount amount & After reprice amt should match....Invoice level sheet - inv# is unique...but in product level sheet have inv# is billed like product level sale and discount amount also distributed product wise....
Here the the billed amount and discount amounts are not matching with 2 sheets. How to find the which inv# product was billed with wrong price or discount...
View 5 Replies
View Related
Dec 22, 2009
(First please don't send me a link on how to subtract time or show Negative time. I know about 1904 ect.. I can't use that. I need a variation on the formula I am using if possible)
A1 I have a time
A2 I have a time
A3 I have a formula subtracting A2 from A1
The times will vary in A1 & A2. Sometimes A1 will be earlier or later thus giving me negative or positive times. I have a formula that will give me a correct answer but will always show either Positive or Neg depending on what I put in the last part of the formula "-H:MM or H::MM" =TEXT(MAX($A$1:$A$2)-MIN($A$1:$A$2),"-H::MM") in A3. I want it to do the subtraction but say if A1 is > than A2 then "-H::mm", If A1 is < A2 then "h::mm". I don't know if that is possibly or how to incorporate that into what I have.
View 3 Replies
View Related
Apr 17, 2009
I would like to modify my INDEX-MATCH formula to "look for" my second creteria in a range of cells (ie. the Type 1 to Type 3 range shown below).
I am stuck with the Type data spread across a range of columns.
InputSite: Island(Criteria 1)Type: Direct(Criteria 2)Cost: $ 10 (Desired Result)Data SiteType1Type2Type3CostIslandDirect $10 MainIndirect $20 SecondaryOther $30
View 9 Replies
View Related
Mar 28, 2013
I have three columns that contain data.
Column A, Column B, and Column C.
Now if data for a certain column is invalid, it will contain text as follows: "N/A". I want to color code the variation existing for the the three columns.
For example:
If on row 3, 3A contains data, 3B contains data, but 3C contains N/A, i want the color of all three cells to be blue (not text color but fill color).
If on row 4, 4A contains N/A, 4B contains N/A, and 4C contains data, I want the color of all three cells to be yellow.
and the rest of the variations as well (5A contains Data, 5B contains N/A, and 5C contains data then all three green etc... etc..)
Of course logically the condition is strictly related to the N/A, and the rest of the data can contain text and numbers if it is not N/A.
Can the color coding be placed in RGB format as well? or can I create a cell with the fill color desired as a reference (in other words a legend that I can refer to when color coding the sheet)?
View 6 Replies
View Related
Jul 9, 2013
I have a list of text values in column X. I need to come up with a formula in column Y.
X
Y (RESULTS OF REQUIRED FORMULA)
Comment
1
HAT
1
First HAT in column
2
[Code]....
I can't play about with the natural order of the spreadsheet, so there's no chance I can re-sort the data into column X and (easily) identify the duplicates that way. So, it could be that the duplicated value(s) will appear in any cell within that column.
I need to identify whether the item is a duplicate in the unsorted list. Ideally, the first entry of a set of duplicates will be given 1, then the subsequent duplicates themselves given a 0 (zero). It's to subsequently do some counts on.
I guess that as long as the one of the entries in the duplicates is marked with a 1, while the others are 0 (zero), that's all that's important.
View 3 Replies
View Related
Mar 17, 2014
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
View 2 Replies
View Related
Feb 3, 2009
Can I get all the information from Employee & Benefits tab to populate in the 401K worksheet. All information is on these two sheets except status, plan entry date, and 2009 compensation.
True under Employees tab means that a person is still active and I would like for that date to populate the 'Hire Date' column; whereas, 'False' represents a person is termed and I would like for the false date to populate under termed. Can you use the social security number if its a V Lookup because I have thousands of rows and multiple people with the same last name.
View 5 Replies
View Related
Apr 22, 2009
How do I write a code for the following Excel formula:
=LOOKUP(E20,{0,2501,5001},{"CatA","CatB","CatC"})
I tried the following but not working
View 3 Replies
View Related
Oct 23, 2008
I would like to do is reference a table of data using the Vlookup function but add all of the results together. I have a table where I keep track of orders being distributed, and in an inventory sheet I want it to reference that table based on the serial numbers entered and total the results.
View 3 Replies
View Related
Nov 21, 2008
A B C D E
1 DAY 1DAY 2DAY 3DAY 4
2 AAA1638
3 BBB7325
4 CCC3847
5 DDD5562
This is what I have. Now I want to see, for each day which has the highest value. so it should return BBB for day 1, CCC for day 2, DDD for day 3 and AAA for day 1.The formula I am using:
=lookup(max(B2:B5),B2:B5,A2:A5)
=lookup(max(C2:C5),C2:C5,A2:A5)
=lookup(max(D2:D5),D2:D5,A2:A5)
=lookup(max(E2:E5),E2:E5,A2:A5)
Tell you what, it is giving me correct result for column B,C,D but not for E. The formula for column E should return AAA, but for some reason it is returning DDD.
View 2 Replies
View Related
Jul 14, 2006
I just wanted to use lookup function in my code. But I had to re-write my for..next statement 8 times. How can I state it in one single lookup function used in a for..next statement. Here is my code
Sub iteration(r)
Dim i As Integer
Dim j As Integer
Dim x1(4) As Double
Dim arra(8, 2) As Double
Dim arrb(8, 4) As Double
r(1) = Worksheets("sonuc"). Range("j4").Value
r(2) = Worksheets("sonuc").Range("j6").Value
Dim myRange As Range
Set myRange = Worksheets("giderler").Range("b6:b11")
x1(1) = Application.WorksheetFunction.Floor(r(1), 0.5)
x1(2) = Application.WorksheetFunction.Ceiling(r(1), 0.5)
x1(3) = Application.WorksheetFunction.Floor(r(2), 0.5)
x1(4) = Application.WorksheetFunction.Ceiling(r(2), 0.5)
For i = 1 To 4
View 6 Replies
View Related
Sep 21, 2006
I have 2 worksheets, 1 containing a list of 5000 products and 1 containing a list of 3 columns with classifications in them (about 300 classifications). for example worksheet 1 has 5 products
Coke
Volvic
Mars Bar
Cornflakes
Bags Maltesers
The 2nd worksheet has 3 columns with the following info
Column 1 Column 2 Column3
(Catagory) (sub Cat) (Sub Sub Cat)
Minerals Soft Drinks 2lts
Minerals Water 1lts
Sweets Choc Bars Std Bars
Sweets Bagged Lines Std Bags
Grocery Breakfast Cereals
I want to be able to add a classification to the products in worksheet 1. For example to be able to choose a dropdown that has a list of each classification
and after coke select from the "Catagory) drop down, then select the "Sub Cat" drop down and then select the "SubSub Cat" dropdown.
Product Catagory Sub Catagory SubSub Catagory
Coke Minerals Soft Drinks 2lts
Mars Sweets Choc Bars Std Bars
As I said I have to do this manually for about 5000 items so I need to be able to do this as fast as I can. Can you use the lookup fuctions or something like that?
View 6 Replies
View Related
Jun 27, 2007
how to fix the REF error in this formula. =IF((OR(G3="Hour (Truck)",G3="Hour (Truck + Dog)")),(VLOOKUP(G3,Rates!$A$186:$B$187,2,FALSE)),(IF(F3="Hanson",3.5,(IF(F3="Lakes",10,(IF(F3="NO",(VLOOKUP(E3,TonnesRates,MATCH(B3,Rates!$B$110:$D$110),FALSE))))))))). I have tried to attach the file with the minimal information in it to make it small enough but I can't get it below 48kb.
View 7 Replies
View Related
Aug 1, 2014
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
View 4 Replies
View Related
Mar 25, 2009
The following Lookup-function does not work properly. Ay idea why? My Input in E13 is months.
=LOOKUP(E13, {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},{"012","001"," 002","003","004","005","006","007","008","009","010","011"})
View 5 Replies
View Related
Nov 12, 2009
I am creating a spreadsheet to keep track of money and grouping them into categories. I have a column to group a purchase into a category (column E) and the amount spent in column F. I want column T to add up all of the purchase that were made according to the category in Column E.
View 2 Replies
View Related
Dec 7, 2006
I have store numbers and store volume groups (one letter indicating their sales volume). This is sorted in somewhat of a table - the volume groups are in row 4 going horizontal, the store numbers are under their respective volume groups going vertical.
I have all store numbers listed vertically on another sheet, starting in A2. I want to search for the store number in A2 within the volume group data and return the appropriate group header when it's found.
View 14 Replies
View Related
Nov 16, 2012
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
View 3 Replies
View Related
Mar 26, 2014
In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.
What would be the best way of getting the invoice number?
View 9 Replies
View Related
Apr 10, 2007
I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.
If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection
If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.
The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.
View 9 Replies
View Related
