# Vlookup Multiple Values In A Range And Sum

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
ADVERTISEMENT
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
Oct 27, 2008

i want to put a subject for a negative values, how should i do it?

For example:

lookup_value: -1000

table_array:

<0 - Poor

0-10000 - Good

> 10000 - excellent

is it possible to do this? ALternatively see the (new) attachment for clearer question.

View 8 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 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
Jan 25, 2014

I am currently working on a project and encountering this problem with VLOOKUP.

I need to show the result into negative since it is negative on the table i made.

In my this scenario, it gives me this error "#N/A" even if the value is in the table.

Here are the details:

3 columns

RANGE Minutes

0.02

0.03

[Code].....

my actual table is upto minutes of +960 and -960 and still planning to go up until +1500/-1500

I have named the table as "TABLE" and the value is on "F3" I am using the command =VLOOKUP(F3,Table,3,TRUE) but if the cell is negative it return positive value how do i change the formula so it return the value to negative if it shoud be negative?

I also attached the file as your reference.

I need this to get the value in minutes if it is within the value in the range.

View 9 Replies
View Related
Oct 9, 2012

I would like to sum the values of a range using something similar to vlookup. The problem with using vlookup is that vlookup returns only the first instance. I have multiple values therefore vlookup would not work. I have 2 variables which is causing my frustration. I have added an example to explain. On the column where it says "correct answer" is where the formula should go. I displayed what the formula should produce so that it explains what I want as the output. The "static column" which contains variables 1 and 2 doesn't change.

The two variables it needs to search for is shown in the variable 1 and variable 2 columns. And the lookup range is on the left.

So basically it needs to match the two variables in the range and then grab the sum of ALL quantities where the two variables match . quantity amount is found on the leftmost column.

Is this possible. sumif works but only with 1 variable.

LOOKUP RANGE

formula column

static column

static column

formula column

static column

static column

quantity

variable 1

variable 2

correct answer

variable 1

variable 2

[Code] ......

View 3 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
Mar 7, 2014

I am attempting to graph data based on work down in specific work centers to track efficiency. I can download all items that were finished for a certain time frame (yesterday, last month, last year, etc.), but the data only shows up as the item, quantity, and the date of closure. I take that info and copy and paste it to the close out tab of the other work book, where it references another sheet to pull in the time required to complete the task, and the work center that did the work. My problem is when an item has multiple work centers on the same item and then taking that info into a pivot table to graph. This can be done manually, but I was hoping I could create a way to only have to copy and paste the data and have it return the work done.

I have included a shortened version of what I am trying to do, eventually there will be 25000+ items on the data tab, but if I can get this to work, then i assume I can extrapolate.

View 1 Replies
View Related
Aug 28, 2009

I have the following 'numerical values'

2510000.011

3010000.011

3510000.011.3

2518000.032.5

3018000.043

3518000.043.7

2530000.125.5

3030000.145.8

3530000.176.2

With the first two values as criterias, I need to find the 3rd and 4th value

Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3

View 10 Replies
View Related
Feb 15, 2010

I tried searching for a solution to my formula problem in this forum regarding vlookup and saw similar formula e.i returning multiple values but it doesn't cater with what I need.

Vlook is returning only the first value but what I need is to return all the multiple return values in 1 cell. I've attached a sample worksheet for reference.

View 7 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
Feb 23, 2012

OK for example if i hae this list of data:

Invoice number Order number

111 560

112 561

112 562

113 563

What i need to do is a vlookup from the above table to return the following:

Invoice Number Order number

111 560

112 561, 562

113 563

View 9 Replies
View Related
Jul 17, 2012

Traditionally the vookup will return the first value it can match. I can return multiple values on different lines using an array formula but this is not useful.

In the example below I have table array A1:B8. Lookup criteria in cell A11 and want to return MULTIPLE values from column B in cell B11.

I would prefer to not use VBA if possible

A

B

1

[Code]....

View 6 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