Free Microsoft Excel 2013 Quick Reference

VLookup multiple values - sum returned values into single cell

I have found several items close to what i need but nothing exact. I need to
perform a vlookup consisting of multiple values (names of cities) then return
the sum of that result into a single cell. Is this possible? brand new to
vlookup so dummy it down for me, please.


Post your answer or comment

comments powered by Disqus
Hi
I'm trying to get multiple values to return into 1 cell.

I have a list I am looking up with a bunch of values that I want to return eg:
Coulmn
A B
1 XX
2 XXX
1 XXXCC
4 XXXXX
4 XXXRMMS
3 YY
7 YYY
2 XXXY

When I do my lookup I want all the matches to collum A to be displayed in a Cell that I choose but they are to display the Value of Column B instead of A.
EG:
Lookup Value 1 would = XX, XXXCC
Lookup Value 4 would = XXXXX, XXXRMMS

How do I get excel to return multiple results into 1 cell seperated by a comma etc...

Hi,

I am looking for a formula to lookup multiple values and return them in a single cell seperated by commas.
A sample of the data is as follows - there are 3 columns:
Code, Batch No, Location
1,2,A1
1,2,A2
2,1,B3
3,3,C1
1,2,A1
1,1,A2

If i entered the formula in a cell for code 1 and batch no 2 I would like it to return A1,A2. I have been using vlookup and if however it only returns the first value not multiple. Can anybody help me?

Many Thanks.

I have a table, listed below, and i need to return the sum of the matching
cells from the lookup. i think its explained pretty well below. The vlookup
formula stops after it finds the first match. i need it to find all matches,
sum them, and return to the cell.

A B C D E
1 PO# AMT

2 1003 75

3 1003 33

4 1006 21

5 1003 19

6 1006 67

Need to return:
Cell A9 -- 1006 - 88
Cell A10 -- 1003 - 127

Currently using the vlookup formula i can only return 75 for PO 1003. this
is because it is the first value listed.

Any clues on how to return all values next to PO 1003 and sum them??

Many thanks.

In a nutshell, what I am trying to do is count multiple values on a sheet and place them into one cell on another sheet in the workbook. Then I want to have drop down lists that displays all of the categories, and depending on the category selected from the drop down list it returns the total for that category.

For example,

COLUMN A COLUMN B
TS 5
SS 10
ST 4
TT 4
SU 0
TU 8
TV 0
TW 1
TX 23
TY 7
TZ 2

I basically need a formula that would go through that data and take all of my (SS,ST,SU) and sum the total for column b's values. So the end result of a correct formula for this would result in 14.

Step #2

I'd like to have a drop down list on a seperate sheet and when I picked a particular category (like Group S) in the drop down list (for example, SS,ST,SU equals Group S) it would display the total amount in the column next to it...total amount being the 14.

Anyone know how to put this together?

Hi guys,
I have just bumped into this forum and I was wondering if you guys can help me with a problem I'm facing in Excel 2007.

So, I have lots and lots of old worksheets, spreadsheets.
I have a column A, each single cell with multiple values (eg. 21; 32; 44). I need to know how can I make the SUM of those multiple values from within a single cell?

You'll probably tell me it's wrong to use multiple values in one cell. The thing is, I can't do anything about it because the number of values in that cell is nerver constant, so it's easier for me to work like this.

Thank you, I really appreciate it.

Hi,

I need your help!
I have a list of managers and a list of employees each with their own superior/manager in a different column.
I want to bring in a column, for each manager, all the subordinates in a single cell, sepparated by semicolon.
For example, in column A I have

Peters
Johnson
Marks
Maning

And in column E and F I have

Smith Peters
Sun Marks
Brucks Peters
Cormick Maning
Kent Johnson
Barnett Maning
Michaels Marks
Mann Johnson

In column B, I would like to have:

Smith; Brucks (for Peters)
Kent; Mann (for Johnson)
Sun; Michaels (for Marks)
Cormick; Barnett (for Maning)

I've read a few additional subjects on look up with multiple values retruns but didn't help much.
Any solution, pls?!

Thank you!!

Assume there is one Excel workbook, with 2 worksheets.

Worksheet1 – properties

Assume two columns, A and B

A B
Id Files
1
2
3
4
Etc
Etc

Worksheet 2 – property files

Assume two columns, A and B
A B
Id Filename
1 123.jpg
2 abc.jpg
2 def.jpg
2 ghi.jpg
3 aaa.jpg
3 bbb.jpg
4 ccc.jpg
Etc
Etc

The idea is that the ‘Id’ is the id of a property, and worksheet 2 holds all the filenames associated with a particular property on multiple rows. So, for example, property 2 has 3 files associated with it (abc.jpg, def.jpg and ghi.jpg).

What I want to achieve, is populate the ‘Files’ cell on worksheet 1 (properties) with the following result:

A B
Id Files
1 123.jpg
2 abc.jpg¬def.jpg¬ghi.jpg
3 aaa.jpg¬bbb.jpg
4 ccc.jpg
Etc
Etc

There can be a variable number of rows in worksheet 2 containing filenames for a particular property. I want to concatenate the filename values, with a ¬ delimiter, only on records that match on id, but the concatenation is within a single cell (ie the Files cell).

So a formula is required that I can copy down the ‘Files’ column, to automatically set the concatenated filenames on worksheet 1.

I am making a worksheet where the some cells may have multiple data values. Is there any way to have multiple values added to a single cell while retaining the ability to analyze the data after.

As an example, a petstore owner wants to create a waitlist database of potential customers who are waiting for certain breeds of puppies to become available. Some customers are only willing to buy a single breed of dog, but others would be willing to buy any number of breeds if they were to come available. Is there any way to input one or more values into a single "breed" field?

Thanks for any and all ideas!

How to Import multiple-line addresses from Word into single cell without
losing line end breaks?

I have a 3 cells that either say "yes", "No" or "Ok" otherwise blank across. I want that value to be returned into one cell Column AB. Basically condensensing into one column like so:

iF(OR(T3={"Yes","OK","No"}),T3," "),IF(OR(V3={"Yes","OK","No"}),V3," "),IF(OR(Y3={"Yes","OK","No"}),Y3," "), etc.

Its returning a #Value right now. Its probably something simple but I cant figure it out.

Thanks for the help!
AESP

Greetings

Was wondering if anybody could help me solve a small problem, the following two:

1) When creating random numbers with RAND formula, it automatically creates it with 3 decimal places, even when I format the cells to use 0, upon summing, it counts in those numbers after the comma: aa,xxx it counts in the xxx's, but I'd only like to use the aa's

2)As shown int he picture, the formula for the total row sum and total column sum in a single cell is not right. The value of cell Q27 equal the sum of Q10:q25 and E27:P27.

Many thankyou's in advance

I have to plot 12 lines on a graph for work, and then manually select the lines to get a power trendline for each plotted line inorder to get the A and B parameters of the equation (y=ax^b) and r-square value. Instead of going through the process of copying and pasting the 3 values for each trendline is there a way to have the values automatically placed into certain cells?

Thanks in advanced
Spencer

How do I for create Multiple Conditional Formulas in a single cell?
For example:
=IF(B50,IF(K5="Short",(L5-M5)*10000,(M5-L5)*10000),"") is one of the
equations, but I need it to multiply by 100 instead of 10000 if the answer is
greater than 1000.
I could also have it so it multiplies the different equation if word
criteria in a different cell from the same row is different. I dont know how
to do that either.

How do I for create Multiple Conditional Formulas in a single cell?
For example:
=IF(B5<>0,IF(K5="Short",(L5-M5)*10000,(M5-L5)*10000),"") is one of the
equations, but I need it to multiply by 100 instead of 10000 if the answer is
greater than 1000.
I could also have it so it multiplies the different equation if word
criteria in a different cell from the same row is different. I dont know how
to do that either.

Hey,

To start im trying to create a sheet that performs a VLOOKUP and pulls MULTIPLE values and returns them into a single cell *info seperated by commas or something*

Some background:

We have a "buildings ship date" sheet that im trying to find all buildings that leave on say 1/1/08 and on a seperate sheet return ALL buildings that are leaving on that date.. into a excel calendar

My goal is to print a calendar that has what buildings are leaving when by using the VLOOKUP function.

I can return the first one it finds on that date.. but if say 2 shipped on that date it only returns the first it finds.. need something that will return ALL finds into the single cell being a date on the calendar. Hope that is clear enough.

Thanks,
Jeremy

I have a cell that has multiple values, separated by ";". For example: "35; 255; 67; 0". In the next cell, I would like to know how can I count these values, except zero. Count not SUM them.

This single cell, can have up to 50 values and each value can have up to 7 digits.

I have data in the following format:

Week, Orders
15, order1
15, order2
16, order3
16, order4
17, order5
17, order6
17, order7

I wish to lookup which orders are due in each week, returning the results to a single cell for each week, eg

Week, Orders
15, order1 order2
16, order3 order4
17, order5 order6 order7

I can return it to multiple cells with an array formula; do I need to continue down that path and gather those results somehow, or is there a better way of going about this?

Hello all,

I have a problem:

I have a cell, ex.
A1 = 1, 2, 5, 8

and I want cell B1 to display their respective names, ex.
B1 = Toyota, Honda, Kia, Hyundia

I know I could do this with a vlookup if each of the values were in separate cells, but since all the values are in the same cell I do not know what I can do, without of course delimiting and vlooking up the details and then concatenating, which would not really work for me...

any suggestions would be appreciated,
if the above could be done in a single step with VBA it would be okay,
but I would rather limit the number of manual steps (text to columns.. etc) as this is only a small step in a larger process.

Thanks,
Adam

Hello everyone,

This is my current situation: I have an excel spreadsheet for my work. On Sheet 2 (labeled DATA) there is every student in my school along with info for each term they were registered (called reporting terms). So ID#'s are listed multiple times in column A in DATA. ex: ID#12345 for Fall2010 reporting term (student enrolled) and ID#12345 for Spring 2011 (student enrolled)

I would use this formula to look up multiple ID#'s and match them with their corresponding counselor. Since counselor does not change across the semesters, it didn't matter if vlookup just brought up the first value.
=VLOOKUP($A$2:$A$8729,DATA!$A$2:$E$65536,5,FALSE)

I created this formula to return multiple values for 1 iD# in cell A2 to show me all reporting terms for that ID#: (ctrl+shift+enter)
=if(iserror(index(data!$A$2:$AB$65536,SMALL(IF(DATA!$a$2:$A$65536=$A$2,rOW(DATA!$A$2:$A$65536)),rOW( 1:1)),8)),"",index(data!$A$2:$AB$65536,SMALL(IF(DATA!$a$2:$A$56000=$A$2,rOW(DATA!$A$2:$A$65536)),rOW (1:1)),8))

This formula works great, but i have to pull down the formula to bring up all the reporting terms for the ID# in A2

I want to be able to copy and paste a list ID#'s in column A and have it return the all the reporting terms for each ID# in column B.. is this possible?

ex: put ID#12345 in A2 and ID#23456 in A3, run formula and get something like this:
A_______ |__B
1| ID#12345 | Fall2010 reporting
2| ID#12345 | Spring 2011 reporting
3| ID#23456 | Fall2009 reporting
4| ID#23456 | Spring 2010 reporting

Any help would be greatly appreciated!

I have also posted this topic on MrExcel.com see http://www.mrexcel.com/forum/showthr...84#post2639584

I had a question on the command for a "previous worksheet". Basically I want to sum up a single cell from multiple sheets onto a summary page. Right now I know how to add cell A1 from Sheet1 to Sheet10, I'd type: "=SUM('Sheet1:Sheet10'!A1)" on a specific cell on SheetSummary.

My question is, if I insert another worksheet between Sheet10 and SheetSummary called Sheet11, is there a different command that I can type so that SheetSummary will automatically pick up cell A1 from Sheet1 to Sheet11? Is this possible in excel? If so, what is the command? "=SUM('Sheet1:???'!A1)"

Any advice will be helpful! Thanks!

I'm trying to sum up a single cell from multiple worksheets that may not be necessarily adjacent to each other. For example, I want to sum up all the A1's from Sheet1 to Sheet100 (excluding Sheet 10, 20, 30.. etc) onto Sheet 101. How do I do that? Is there a command which signifies "exclude"? Please help!

Hi,
I'm currently using the following code for counting the comma seperated values entered in a single cell (got this code from one of the threads). Could anyone help to convert the same into a macro?

=IF(A2"",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1,"")

Instead of counting values in a specified cell (in this case "A2") if the macro shows pop-up asking for which cell's data to be counted....would be more helpful.

first off... i think its rediculous you cant say "Help", "need", or "how" in the title?
"How" are you suppsed to tell people you "need""help"!?

anyways...

i have a table that is linked through a live database. this table gets refreshed and filtered based on what the end user is looking for.
"PLEASE", I "NEED" "HELP" on "HOW TO" figure a formula or etc that will search column "a" for all instances of a value, and return column "b" for each instance...

i have alot of other things going on in this sheet with regards to macros and other lookups at this time, but i cant figure this part out.

IE:

| Job Number | Part # | Description |
|1000-0 | 0987 | black |
|1000-1 | 1234 | green |
|1000-1 | 2468 | blue |
|1000-1 | abcd | yellow |
|1000-2 | 3579 | amber |

my user will type a job number into a given cell: 1000-1. I need to look up the value of that cell, and return the value of the part number to cell "1" and the description to cell "2"

The general purpose of this sheet is to pull up the inventory of items used in production of that job.

Any "HELP" is greatly appreciated.

Thanks,

Dave

Hello All!

I was wondering if it is possible to have multiple font colors in a single cell.
Such as:


	VB:
	
ActiveCell.Value="Red & Blue" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where the font for the word Red would be Red and Blue would be Blue.

Much thanks to anyone who may provide help.


No luck finding an answer? You could always try Google.