VLookup Multiple Values In Table - Not Working
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
ADVERTISEMENT
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
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
Mar 5, 2009
embedding the following
(VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE)
in the following statement:
=IF(((VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE))-A2=0),(VLOOKUP(C2,[svdlsa08_2007mast.xls]Sheet1!$C$2:$U$3869,17,FALSE)),"No Match")
The IF statement is designed to check a different value in the source spreadsheet against one in the destination sheet which should always match for the desired values, but this doesn't seem to make any difference to what is being returned - I'm still getting the first match instead of the appropriate one.
Is there a better way to force the VLOOKUP to return the value from the correct match and not just the first one?
View 9 Replies
View Related
Nov 14, 2012
I have a vlookup formula: =VLOOKUP(TRIM(C101),Sheet2!$A$1:$B$500,(2), FALSE)
It is not working with General number values. Why is this? Is there something I should add?
The formula works if I swap columns to one with text or if I include text in the column.
View 7 Replies
View Related
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
Aug 8, 2006
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
View 5 Replies
View Related
Jul 23, 2014
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
View 8 Replies
View Related
Jul 13, 2012
I want to find out how to lookup a table in excel and add all the like named values together.
Example:
Max 17
Tom 4
Chris 20
Max 10
Jim 12
Tom 15
So the function I want is to have a chart at the bottom of my table totaling all the values for each name
Max (add all max's numbers together) and so on down the line
View 3 Replies
View Related
Jul 27, 2007
I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example:
I want to look up the string "Example1" and the lookup range is
Example1 - monthly
Example2 - weekly
Example3 - weekly
Example4 - monthly
So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)
View 6 Replies
View Related
Mar 30, 2009
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
View 4 Replies
View Related
Jul 17, 2013
I have a question dealing with a variable column and cell reference I'm using. To make it simple here's what I have done. On the FRUIT tab I have the following table in location BU132 - BV161
Name Column
Apples A
Pears B
Corn 'VEGGIE'!A
Peas 'VEGGIE'!B
I'm using the following formula to get the location of a cell on both the FRUIT and VEGGIE tab that has the value.
=INDIRECT(VLOOKUP(CH61,$BU$132:$BV$161,2,FALSE)&CN61)
{where CH61-CH100 contains fruits or vegetables and where CN61 is a table that has the column reference which goes from 1 to 40)
This formula works great as long as the item is on the FRUIT tab. I can just drag the formula down the entire column and get my values for all the FRUITS listed in CH61-CH100. But, it doesn't work for any items in CH which are on the VEGGIE Tab. What syntax do I need to use in the cell next to Corn and Peas above to get this to work right?
View 2 Replies
View Related
Sep 13, 2012
I am trying to simplify a complex array formula using a UDF. I am trying to return multiple corresponding values horizontally. Can this be translated to VBA?
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
A11 is the lookup value
A2:A8 is the range where A11 is located
B2:B8 is the value I want to return
View 1 Replies
View Related
Nov 26, 2009
I've sorted the second row by price on the second sheet, but how do I get the corresponding category and quantity numbers? A normal Vlookup formula would return '8' twice on (a) and (b), but I need both 8 and 6... So only one value should be returned at both (a) and (b), as long as all different quantities are covered.
View 5 Replies
View Related
Oct 20, 2012
I have made a spreadsheet where I want to input data and transport it to a rent card information.
I want to use vlookup to find the apartment number in a different spread sheet and fill in the information on a different spreadsheet (rent, fees, utilities).
I get how to use vlookup to get one value but I need 10 values to return.
View 1 Replies
View Related
Jul 6, 2007
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 5 Replies
View Related
May 14, 2014
vlook up.xlsx
View 14 Replies
View Related
Jun 27, 2014
I have a list of countries in column A, there are duplicates in this list. In column B are numbers corresponding to the country.
A B
IRELAND 1221
GERMANY 2341
USA 3443
IRELAND 2231
Ireland could be in the list and have up to 20 different values beside it in column B
On a separate sheet i have a drop down list of all the countries. I am looking for a vlookup or something similar to return a list of all the numbers beside IRELAND when Ireland is selected in the drop down list.
I had been trying a vlookup but this looks at the list and stops at the first "IRELAND" it finds and just returns the value beside the first "IRELAND"
View 3 Replies
View Related
Dec 6, 2013
i have a set of data in which i'm trying to lookup a value from a range of file #'s (ex 100-125) that i have concatenated with a file ID (ex 123456789). as of right now i have to vlookup each individual concatenated number, return the result value in its own column (one for 100, one for 101 and so on..) and then sum all 25 columns to get the final number) is there a way to vlookup the range (ID+100:125) and sum the results?
here's the formula i use for each column starting with 100, A2 representing the file ID, C:O being the array
=IF(ISERROR(VLOOKUP(A2&100,$C$2:$O$10000,13,FALSE)),"0.00",VLOOKUP(A2&100,$C$2:$O$10000,13,FALSE))
View 14 Replies
View Related
Dec 15, 2013
Trying to pull data from one workbook to another using Vlookup.
The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.
I know I require a nested formula but cannot seem to get the variables correct:
View 2 Replies
View Related
Jul 22, 2014
formula to vlookup and return multiple value in a single as per the attachment.
View 7 Replies
View Related
Apr 7, 2009
I have a workbook with 2 sheets. The sheet called 'data' contains all purchasers names (which do get repeated), the item they bought and 3 item costs, all in range A1:E15. The 'lookup' sheet allows the user to enter a purchasers name in cell B1, then I want all the items that purchaser bought listed with the corresponding item costs. I've attached a sample spreadsheet.
View 3 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
Dec 25, 2013
I am using the VLOOKUP function to get the exact corresponding value in one particular column. But I need to have two different value in two different columns. Here is an example.
I have attached the excel sheet as well.
Actually I do send the data to my different colleagues and they do send me back with the responses in two different column of D and E corresponding to the "ID" specified in column "B". Which I do need to MAP individually. But I want Excel to map the value of that "ID" (column B) to the corresponding responses in Column D and E with formula.
VLOOKUP generally gives corresponding value to either column D or E but i want both at a time to save time.
UPLOAD_DATA.xlsx
View 5 Replies
View Related
Jun 3, 2008
I'm having trouble getting my payroll data to combine. I've been using VLOOKUP to look up employees by their Personel Number (Column A) and return their name (column B), Cost Center (Column C), the Fund that they are paid from (Column D) and the Amount they are paid (Column E). The problem is that some employees are being paid out of 2 or more funds and VLOOKUP is only returning the fund that appears first, leaving out the other portion of their pay and thus making my grand total quite far off. How can I get Excel to return all the entries for a particular Personel Number? There are more than 16000 employees, so this is no small spreadsheet.
View 14 Replies
View Related
Dec 19, 2011
I've been trying to research vlookup to see how I can use it or something like it to return multiple value for a dynamic chart I'm trying to build. I've found several paged to give examples but I can not seem to figure it out with my sheet b/c the examples differ from mine I have a financial spreadsheet with several categories and I want to make a chart for all my utilities. i need a array formula (i think) that will search for (example) My salary, my wife's salary and other income and extract those values in the next column (Credit (+)) to build a chart... (i will use this same concept to make other charts as well.) The Categories column is F and the values will need to be extracted for the chart are in G. doesnt matter where they are extracted to but for the sake of being speific ill say starting P50.
(first row below headings is row 5)
CATEGORYCREDIT (+)DEBIT (-)Salary - Byron$ 2,571.11 Utility - Electric$ (154.25)Salary - Felicia$ 1,120.20 Utility - Gas $ (79.47)Mortgage$ (852.01)Salary - Byron$ 2,499.53 Other Income$ 150.00
View 3 Replies
View Related
Apr 8, 2008
I am up for any way that this would be possible ....
View 17 Replies
View Related
Apr 18, 2014
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
View 9 Replies
View Related
Jun 23, 2014
I'm trying to use vlookup to return multiple values for the same unique identifier. I've read a couple of examples within the forums, but I haven't been able to find anything I understand. I have employee ID's and these ID's have specific access associated with their ID's and each access is listed within a different row. Within a new spreadsheet I removed all duplicates of the employee ID so now I no longer have a one to one match between spreadsheets, so I would like to create a vlookup that will list all access associated with each employee ID. I have attached a sample of the data sheet I am working with.
View 5 Replies
View Related
Jan 15, 2014
Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.
Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.
View 6 Replies
View Related
