I'm using VLOOKUP with MATCH to find data in a table. I now have to add a third condition to the look up. I've attahced a file as an example. I'm not sure how to list the data for the third condtion. I created another table with the data for third condition.
View 2 RepliesI am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to insert a formula to find the name (match?) from one column on say sheet 3 and return the value in the next column.
column a sheet 1
my name
your name
someone else
I would like it to search sheet 3 column a and return value sheet 3 column b
as sheet 3 would look like this
col a / col b
john q / 11a
my name / 13b
someone else/ 9a
your name/ 12b
What my goal is - is to only have to enter the column b in sheet 3 one time per name and have it fill in on sheet 1 for every entry of multiple entries of the same name in column a if that makes sense.
I know this should be simple- I want to avoid the if statement as there will be about 50 names to sort and return the proper class (column b)
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedIm using vlookup to search a table and fill out a calendar with dates that various employees are gone. They can have multiple trips in one month and thus will be in the table multiple times. I have the calendar set up so days go across the top and names go along hte left side. so each cell is identified by name and day. I then have each cell using the vlookup command to search the table. now if one person has multiple trips a month is there a way to compare multiple entries in the table?
View 14 Replies View RelatedI'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time
LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No
Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?
This had been hunting me for weeks and I still could not come over it after weeks of study the conditional formating IF, match, index, Vlookup, etc to find several duplicated value, e.g Column B = Name 1 & Row B = Time and so on, where as Name will have data of Name 1 duplicated in different time, while row B, "Time"would change to a different Name and maybe it would go back to Name one.
My problem was dont know which formula I can use in a different sheet to bring back the data to Name 1 did Task A at Time A, while Name 1 did Task B at Time K, etc. If this is not Name 1, then Name B replace above.
I need a formula for F3 that will round-down E3 and find its corresponding row in the table to the right and find its intersection with the coating listed in I3. Does that make sense?
I've tried, to no avail:
=VLOOKUP(E3,$J$3:$Q$19,MATCH(I3,$K$1:$Q$1)+1)
=INDEX($K$3:$Q$19,MATCH(E3,$J$3:$J$19,1),MATCH(I3,$K$2:$Q$2,0))
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
View 9 Replies View RelatedI want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View RelatedI have an excel workbook with 2 worksheets. One worksheet shows the MASTER LIST of COMPLETE Customer Names (e.g. ABB Supplies Incorporated). The other worksheet has information on customers but the customer names typed in are incomplete (e.g. ABC Supplies). I need a macro that would look do a comparison of the customer names in the 2nd worksheet to the Master List worksheet and pull the data (complete name, address, etc.) for those that would match (partial match since company name is 2nd worksheet is usually incomplete).
View 2 Replies View RelatedI have a volumes sheet and based on two criteria (in columns) am trying to pull the corresponding value from the April Results Table. I have product type in C2 (this varies down the column) and depot numbers in D2. The table i am trying to match has depot numbers in column A2:A93, and Product type in Row 2 (B2:P2), where the two criteria are matched, i need it to return the corresponding figure. I have tried various combinations of Index and Vlookups but i cant get it to work!
View 5 Replies View RelatedI have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.
I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.
I need a little coaching on big data. I have two reports, one from a production system the other from the invoicing system. I need to reconcile the two reports to ensure our invoicing ties out to production. At this time I am focusing on 1 particular item code, but there are potentially hundreds of item codes.
I have 1 Item Code.
I have a around a hundred invoices. (Sample 100)
I have a few hundred different customers. (Sample 100)
I have several hundred lines of billing data. (Sample 650)
I have several thousand lines of production data. (Sample 40,000)
In the billing system, per invoice I might have 1 or more billing lines for this item, usually no more than 2.
In the production system, per invoice I might have 5 to 50 or more production lines for this item per invoice.
Example
Prod System:
30 lines of data has a run amount and a total run amount for each line.
I am looking for tools to reconcile this data. VLookup and Index/Matching is not cutting it. VLOOKUP does not move beyond the first line found. Index/Matching wants to add all the prod together. I can get subtotals easy enough. I have tried Concatenation, but when it takes multiple production data lines to equal 1 billing line, this does not work. I needs some thing that can look at the Qty on a billing invoice and tie it out to various lines of production data to identify which production jobs went on that line of the invoice.
Here is a sample. These are fairly easy to resolve, but others are not so easy due to number of production and billing rows.
Invoice number
Billing item
Est. Quantity
Actual quantity
Billable Qty
Sales order
Position number
Sequence
Agreement
Billing source
Description
Total Billable Qty
[Code] ..........
I humbly seek assistance and help to fine tune my "find and match" function that works between 2 files. I am using the following:-
=INDEX('[Jan 2009 Sales & Gross Profit Record.xls]PG'!$D$3:$D$12,MATCH(C4,'[Jan 2009 Sales & Gross Profit Record.xls]PG'!$F$3:$F$9,0))
My problem is I am only able to do it in D9 where customer and amount are matched correctly. But when customer appears more than one then it doesnt match corectly.
I have a sheet (name “master”) that I need to compare against data in a table. For each row, I am looking at two columns of information that I need to compare against the data table. The columns are (1) “State of Sale” and (2) “Associate ID”. I need to determine if the associate is allowed to make sales in the state that is identified, which requires looking at the data sheet.
This steps I need to take are as follows:
1 – grab the associate ID and determine if it is in the data sheet. If the ID is not in the sheet, then “No record found”. Else, go to next step
2 – if the ID was found in the data sheet, then the next step is to grab the “State of Sale” and compare against the data table. Go to the row of ID in question, and then go to the column of the State code in question. If there is an “x” in the intersecting cell, then sale is “ok”. If there is not an “x”, then “sales violation”.
IF/ AND/OR
I have 6 tables on one sheet that I want to match data and print out the name of the relevant table ie brown, white ,pink etc
There needs to be a data match in both columns of a table before it prints the name of that table.
I have tried the following but haven’t quite got it right:
IF(AND(B3>9,B349,E39,B327,E3
I have a Takeoff sheet where an estimator enters a part# and the rest of the data is entered on the Takeoff sheet by using Vlookup to retrieve the information from the Products Sheet (there are about 12,000 products with 12 fields for each product). I need to then take all of the parts on the Takeoff sheet, and summarize the data, which is what Pivot Tables are for. The thing I cannot find out, is if this can be done. The machine keeps running out of memory (it has 32g, so I question that, and I tested with a 500 row sheet and got the same result), so I can never get the Pivot Table to actually create, or get a useful error message. Searching the web and this forum tells lots about Vlookup IN a Pivot Table, but not creating a Pivot Table from Vlookup data. I copied the data values to a new sheet to test the data and the Pivot created fine, so I believe the data itself is properly formatted.
View 1 Replies View RelatedI have a workbook where in Sheet1 Cell A1 is a customer name, and A2:A5 contain parameters for the customer. I would like to be able to input those data to a Vlookup table on Sheet2. (The Vlookup is used to fill values on Sheet3).
I have been trying to create a macro to copy those parameters from sheet1A2:A5 by finding the match for A1 in the Vlookup table in Sheet2 and then copying to the corresponding matching row , A2 pasting into column 3 (on the corresponding row of course), A3 into colum 4, A4 into column 5 and A5 into Column 6.
A newbie here.
I have numerical data in
range c3:c12
range f3:f12
Based on above two criteria I need to retrieve data in a Vlookup table, range gr1:gr510.
The Vlookup table range is gp1:gr510
I am wondering if it is possible to search a column full of assorted values which is unsorted, and then return the closest value.
I have written a previous thread which was similar, but misleading, i feel this thread is more to the point.
Currently i use this code, but it only finds the closest highest value if the data is sorted in ascending order.
How do you locate cells that have matching content within strings
Need to be able to match at least 5 consecutive characters within cells of both columns....
- I copy some records from a Database into an Excel sheet in workbook (say W1) in the Worksheet WS1.
- The i look up for values in a column (say column B) of W1 in another worksheet in another workbook(say W2) .
- If i find a match in Workbook 2(W2) ,Worksheet 1 , Row 2, Column A ; i look at the values in the Column B, F,G on the Row 2 itself.
- Each of these values can be found in the corresponding Worksheets 2;3;4 in the Workbook2(W2) .
- Then i need to pick up all these values from worksheet 1;2;3;4 in W2 and contatenate them and put them in the Workbook W1 against the Row .
- Similarly i process all the non zero rows in the workbook W1.
I have run a pivot table state, city, address, floor and all the maths works out fine.. but the name in the 'total' rows, does not match the name in the data rows.. e.g. texas, dallas, 57 oilrig street has total rows saying CA toal,Newark total, 560 mission street total (obviously split across the appropriate rows for each total) but in each case, all those square feet, or dollars add up perfectly, for the data rows above them. double clicking the totals shows no sign of the rogue location... but these same entries are in EVERY set of totals in the pivot table!
POSSIBLE clue.. I generated the pivot at home, on Office 2003, and am now viewing it at work in 2002 version. (buit this could have nothing to do with it at all. Am I missing something obvious, or do i have good reason to pull my hair outr in frustration (first time this has happenned in a year of doing these reports daily)
I have a workbook with 32 worksheets. Data on 31 of these sheets all filter through to the one "Summary" sheet. The data on all the other worksheets is input manually and the lay-out is identical on each sheet. What I want to do is a Vlookup on the summary sheet for every sheet in the workbook, but without typing VLOOKUP(A1,Sheet1A:C,3,0)+VLOOKUP(A1,Sheet2A:C,3,0) etc etc (my sheets aren't actually called Sheet 1/2/3 etc, they have specific names).
View 4 Replies View Relatedon sheet 1 in cell C39 i need to find data from sheet 2 in row B that is 534 as in C32 is 11.2 and on sheet 2, 11.2 equals 534
View 2 Replies View RelatedThe table to the right is actually in another workbook, both workbooks will be linked. What I'm trying to achieve is a vlookup formula that will count the items in the left table that have numbers entered into them. (This table will be located within a different workbook).
I have a large amount of data Range A1:DA6000 on Sheet2 in the following format:
120200 1885 | 42.43 | 130112 1992 | 49.65
120200 1886 | 45.95 | 130112 1993 | 48.08
120200 1887 | 47.65 | 130112 1994 | 49.77
120200 1888 | 45.97 |
120200 1889 | 48.34 | 130133 1887 | x
120200 1890 | 49.42 | 130133 1888 | 41.6
120200 1891 | 48.85 | 130133 1889 | 45.45[/list]
Cell A1 Sheet1 will be variable and I need to find the same value on Sheet2 in all that data and copy the number in the cell to the right to the clipboard. For example:
If Sheet1 A1 = 120200 1885 I want to find 120200 1885 on Sheet2 and copy 42.43 to the clipboard
Or if it's 130133 1887 I want "x" copied to the clipboard and so on.
I think I want to have a macro "Find" the cell and copy the one next to it, but I don't know the syntax to do that. The macro recorder only takes me part way.
I can't figure out how to make a VLOOKUP formula work, as there's way more than 65,000 lines to make a lookup table out of the data.
I am going to explain the issue with a photo linked below
Capture2 | Flickr - Photo Sharing!
Basically i want to match data from column one with data from column 3 if true then copy the data in column 2 to columns 4 in the same match row.
I am needing to find a specific row of data in one workbook and, once found, copy and paste the values into another workbook. The worksheets in both workbooks are formatted exactly the same. Only the first eight fields of the row need to be copied (e.g. A7:H7, A150:H150, etc.)
Will a combination of VLOOKUP and ROW do this and how do I get them to work together? Once the correct row is found, how are the fields selected, copied and pasted into the specific worksheet of the other workbook?