Find Value In Table With Multiple Values?
Feb 18, 2014
What I'm looking for is a formula that will search a data table on a source tab and find a value in a column. And have the ability to function like a vlookup and display the contents of cells in the same row on a different tab. The catch is the value will repeat it's self at random interval in the data table. So for example the values are ON-1 to ON-8 and MB-1 to MB-8. Each value will have it's own tab and I want the formula to search the Source tab and capture the value in order but on the next row of the spread sheet.
View 4 Replies
ADVERTISEMENT
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx‎
View 4 Replies
View Related
Jun 24, 2014
Vlookup a table, to another table that has multiple values.
E.g.
Table
GPASP002
Look up table - need to return second coloum but not the first value, all the values?
GPASP002 KZASP100
GPASP002 KZASP500
GPASP002 KZASP600
GPASP002 KZASP501
GPASP002 KZASP502
GPASP002 KZASP601
View 2 Replies
View Related
Jul 9, 2014
I need a find the intersecting price values for any given height and width. From what I have read on the internet, I can use the "=INDEX( ),MATCH( ),MATCH( ))" formula. However, this doesn't seem to work because the height and width is separated into intervals.
View 3 Replies
View Related
May 3, 2013
I'm trying to come up with a formula to find and proper sum the value in a table like this one:
Month/year
1995
1996
1997
1998
Jan
3,63%
0,86%
3,5%
1,5%
[Code] .........
But the actual table goes till december and the years till 2013. For instance, if I need the cumulative index from, let's say, jan/2010 to feb/2013, I'm having trouble with this formula. I can't get excel to sum them properly.
View 1 Replies
View Related
Apr 24, 2013
Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.
Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?
Total weight
Point
Insert
Nock
Shaft
?
100.1
109.7
20
179.1
100.1
110
View 5 Replies
View Related
Jun 9, 2014
I am pretty new to VBA and have been wrecking my brain and reading just about every Thread there is on this and still can't figure out why I am not getting the code to work.
I am trying to get data from Column "Sale Price", stored in Table "MasterInventory" on worksheet "Master Inventory" to populate a textbox in a UserForm by means of Vlookup.
Upon running the code below I'm getting Value Error 1004, and during Debug when I hover over "MasterInventory" it shows "MasterInventory=Empty"
Also Im trying to figure out how to do it so I can call the "userform" up from any worksheet and add the entries in the table on worksheet (Jan, Feb, Mar, etc.) for the month depicted in the TextBox "Date" on the Userform
The code looks like this:
Private Sub CBx_PROD_AfterUpdate()
'lookup value in Col F [Sale Price] based on Product (Col A [Description] in Table [MasterInventory])
With TB_SP
If OB_Y.Value = True Then
Me.TB_SP.Value = Application.WorksheetFunction.VLookup(CBx_PROD.Value, MasterInventory, 6, False).Value
End If
If CBx_PROD.Value = "" Then
Exit Sub
End If
End With
End Sub
View 6 Replies
View Related
Jul 5, 2012
I have a table refreshed from a MySQL database that I use to create a report. I can use this data quite well using formulas in the cells.But in order for me to add more information to the report I use without making it cluttered I need to add some data as comments.
I have created a loop that picks up some cell values that I want to use as criteria but I cant work out how to use these variables to search through the table and get the info I require to put in the comment. In a cell I would use offset/match or index/match to get the required cell value but how do I do this in VBA?
View 2 Replies
View Related
Jul 4, 2006
I have a giant lookup table with five columns. I have three variables, and I need to find the row in the lookup table where the variables match the first three columns of the lookup table. ie. Find the row where variable 1 matches the value in column 1, variable 2 matches the value in column 2 and variable 3 matches the value in column 3. If this row exists then return the value in columns 4 and 5 of that row. Otherwise, return a zero.
View 4 Replies
View Related
Aug 5, 2009
I have run into serious trouble with a file I am working on. I have 12,000 companies (roughly), and each of them appears in an Excel file multiple times (some 1 time, other 18 times) with a few parameters. The big file (about 90,000 entries) is sorted so it looks like below:
I will illustrate for 2 companies:
Company A 0 200
Company A 0 0
Company B 300 400
Company B 0 50
I need for every company to do a countif for each column on condition
that the value is larger than 0.
In this case I would end up with:
Company A 0 200
Company A 0 0
Countif result 0 1
Company B 300 400
Company B 0 50
Countif result 1 2
However, I need to extend this to the whole 90,000 entries and 12,000 companies and this cannot be done manually. I thought about vlookup but that returns the first entry only, and I have multiple.
View 4 Replies
View Related
May 12, 2009
I have 2 columns, A (has the names of employees) and B (has the month in which the employee has a project scheduled). What I’m looking to do is find the latest month an employee has a project scheduled. Note: Employees can have a multiple number of projects so they may be listed multiple times with corresponding months.
I have each employee listed in column F and am looking to find their latest project month in column G. (I have the number that the formula should return in column H).
View 3 Replies
View Related
Aug 12, 2006
I am looking to find a cell value on a spreadsheet that has cells with multiple values listed in it. For example. If I CTR F for i.e. (745) on a spreadsheet that has this value in a cell that contains i.e. (684, 362, 745, ABC) all in one cell I get a no results. Ultimatelly, I'd like to run a Vlookup function on this file.
View 7 Replies
View Related
Jan 10, 2008
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2".
Compare the corresponding values in Column B, and multiply the lowest value by .2
I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
View 6 Replies
View Related
Jan 3, 2014
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
View 2 Replies
View Related
May 3, 2009
I have a sheet, called "output", in which I need to complete column C "calculated values". I need to complete the table based upon a formula table (which is in sheet "formulas").
For the first row of data, cell C2, I need to take the price per order ($0.25; cell A2) and number of orders (40; cell B2) and copy data to cell B4 and B6, respectively.
Once the data has been copied to to cells B4 and B6 on the fomulas sheet, I need to copy the calculated value in Row N to the output sheet. Note that the value being copied from N can be N11, N12, N13, N14, or N15 (the one that is <> to null).
View 2 Replies
View Related
May 20, 2013
I'm trying to get a more manageable formula for totaling multiple values in one box using Vlookup. For clarification this is what I mean:
On spreadsheet A, I have this table:
[IMG][/IMG]
The value in B2 is the name of the item I am making and the items below are the components required to manufacture said item. In order to build them, I need so many (Column E) and I have indexed my remaining values simply by doing =E3-H3. To build each component requires minerals which are listed on a seperate sheet (Sheet B) as such:
[IMG][/IMG]
The only Value in the above table I am truly interested in is "Current" (E Column).
Back on SheetA, I have a second table which adds all of the component minerals up for a grand total of the minerals required to make the item in B2 as such:
[IMG][/IMG]
Now, this is my issue: The code for N3 is incredibly unwieldy:
=IF($F$3>0, $F$3*VLOOKUP($L3,
'Component Materials'!$A$15:$E$21,5,FALSE))+IF($F$4>0, $F$4*VLOOKUP($L3,
'Component Materials'!$A$25:$E$31,5,FALSE))+IF($F$5>0, $F$5*VLOOKUP($L3,
'Component Materials'!$A$45:$E$51,5,FALSE))+IF($F$6>0, $F$6*VLOOKUP($L3,
'Component Materials'!$A$55:$E$61,5,FALSE))+IF($F$7>0, $F$7*VLOOKUP($L3,
'Component Materials'!$A$65:$E$71,5,FALSE))+IF($F$8>0, $F$8*VLOOKUP($L3,
'Component Materials'!$A$115:$E$121,5,FALSE))+ IF($F$9>0, $F$9*VLOOKUP($L3,
'Component Materials'!$A$135:$E$141,5,FALSE))
I set this up to look at a specific value and if it was >0, it would multiply that value times whatever value it found on SheetB ("Component Materials"). The size of this formula makes this difficult to migrate to further items, so I would like to reduce the complexity of the formula.
View 7 Replies
View Related
Jun 13, 2013
I am trying to use a =vlookup(and( type formula. Excel accepted my syntax, but the formula is not working. Here are the details:
=VLOOKUP(AND(E2,O2),'[SOURCE DATA FOR PROFILES.xlsx]Sheet1'!$B:$G,6,FALSE)
The values from cell E2 and O2 are both present on a row in the SOURCE DATA spreadsheet, and column 6 is the last column in my table (both values appear before column 6, but not next to each other).
The formula returns #N/A. Under those circumstances, shouldn't it return the value of column 6 on the row where those two values are present together?
Do the columns containing the values have to be next to each other in the table for this to work? Does the data type matter (one is a number, the other a street address)? The value to be returned is a number.
View 5 Replies
View Related
Dec 11, 2009
I have a spreadsheet with two different rating scales (People & Business) that have a value of 1-5 per person. From this I created another column 'Sorter' that gives a person a single value of 1-25 (5*5 possibilities from the two rating scales.) I am trying to place people into a table based off of the column 'Sorter' as shown in columns U-Y. The real table cleaned up is in the table tab.
View 5 Replies
View Related
Jan 5, 2007
the formular that i have in the attached file is returing all the customer numbers
Is it possible to return only one value of each
the data is in the "data" sheet and the formulars are in sheet1
View 3 Replies
View Related
Aug 1, 2008
Is there a way to drill down the data of 100 values in a pivot table into one worksheet without resorting to drilling down each value, having 100 worksheets for each value, to paste into one worksheet? I got my company to give me an experimental computer to test for this in 2007, though if there is a way to do this in 2003,
View 4 Replies
View Related
Aug 23, 2013
I have 2 sheets that i need to find what the same valued cells are.
1 sheet has names and i need to find the rows where those same names are on the 2nd sheet.
The formats are different (upper-lower case) and there is additional text as part of the cells in the 2nd sheet. (i.e prod123.web.corp.com) and i just need to find the prod123 parts.
View 9 Replies
View Related
Mar 24, 2014
Column A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
View 2 Replies
View Related
May 22, 2009
I want to find sum of all 4 returned values (Sales!BL5, Sales!BM5, Grants!X5, Deferred Pymts!X5
Column References:
Cell AD2 (AMOUNT TOTAL PYMTS)
Cell AC2 (TOTAL NO. OF ORDERS)
Cell Range B5:B4833 on multiple pages (CUSTOMER NAME)
Cell Range AJ5:AJ4833 on multiple pages (CUSTOMER NAME)
Formula:
In Cell AD2 of CustomerData! Sheet:
If AC2 is not blank, then
look for match between A2 and Sales!B5:B4833, return the value in Sales!BL5, and
look for match between A2 and Sales!AJ5:AJ4833, return the value in Sales!BM5, and
look for match between A2 and Grants!B5:B4833, return the value Grants!X5, and
look for match between A2 and Deferred Pymts!B5:B4833, return the value Deferred Pymts!X5
Hope this does not confuse, but if John Doe is listed in AC2 I want to find the sum of his total payments if his name is listed as a customer in Sheets Sales, Grants, and Deferred Pymts. The Sales! sheet is divided into AM and PM pricing which explains why Sales! has Column B and Column AJ for Customer Names.
View 13 Replies
View Related
Mar 31, 2009
I am trying to find 3 values (X,Y,Z) in row A and concatenate those three values in column A for the entire range below, say A2:A100.
View 9 Replies
View Related
Aug 19, 2008
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
View 9 Replies
View Related
Aug 22, 2013
SM extract 8.21.2013 Cust-sample.xlsx
I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"
The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots
View 8 Replies
View Related
Mar 13, 2014
So I have a list of organizations in Column A, with multiple names (anywhere from 0 to 50 names) for each org. in Column B.
Org
Names
Company A
Brown, Jones, Smith, West
[Code]...
I want to do a find and replace for all of Column B, where all the names are replaced with their respective color values. If possible, I'd like this to all happen with the individual cells (so for example, B3 might go from "Brown, Jones, Smith" to "Red, Red, Blue").
If that's not possible, I could divide all the names into individual cells and then find and replace.
View 3 Replies
View Related
Aug 21, 2006
As in attached file, I have 5 tables:
Table 1: matrix that contains Weight data in each cell
Table 2: matrix that contains Code data in each cell
Table 3: Relationship between Code and Level data
Table 4: Table with relationship between Weight data, Code data and Level data
Table 5: I want to populate each cell in Table 5 matrix from getting values from Table 4 by looking up the right value from Table 5 based on :
1) Weight data from corresponding cell in Table 1
2) Code data from corresponding cell in Table 2
3) Code and Level relationship.
for e.g. to get value for Table 5, Worksheet cell B140 ( intersection of A1-A1),
1) corresponding weight is 1.17 as in Table 1 (Weight data) (Worksheet cell B3)
2) corresponding Code is 6 as in Table 2 (Code data) (Worksheet cell B35)
3) Code 6 corresponds to Level 2 (as seen in Table 3)(Worksheet cell A74)
so, looking up the 3 values from Table 4 i.e. Level 2, Code 6, Weight 1.17, one gets cell AR98 with corresponding value. therefore, final value in Table 5, Worksheet cell B140 (intersection of A1-A1) will be equal to $4.00
I have been trying to solve this using Vlookup, Hlookup, Index/Match but am getting confused. Also, not too sure if this can be done in 1 step. New to these excel and these functions
View 2 Replies
View Related
May 20, 2008
I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.
View 9 Replies
View Related
Apr 17, 2014
I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.
I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.
View 9 Replies
View Related
