VLOOKUP to return Cell Address

I have a table, and I want to do a Vlookup on Column1, and when I find what I 
am looking for, I want to sum column 2 from that row down to the bottom of 
the table.

For example,
Item 1     2345
Item 2     3478
Item 3     1298
Item 4     123
Item 5     1233

I want to find "Item 3" and then I want to be able to return the sum of 
(1298+123+1233).

How would I do this? My thoughts were that I would do a lookup to get the 
row numner of "Item 3" and then do a sum() with the cell range starting from 
the row where I found "Item 3" but I'm not sure how to do that.

Thanks,
Steve
0
Utf
1/20/2010 1:51:03 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
2289 Views

Similar Articles

[PageSpeed] 19

Hi,

I may have overdone this, i'm sure there's a simpler solution but in the 
meantime try this

=SUM(INDIRECT("B" & MATCH("item 3",A:A) &":B"& MATCH(LOOKUP(10^99,B:B),B:B)))

Mike
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Steve Haack" wrote:

> I have a table, and I want to do a Vlookup on Column1, and when I find what I 
> am looking for, I want to sum column 2 from that row down to the bottom of 
> the table.
> 
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
> 
> I want to find "Item 3" and then I want to be able to return the sum of 
> (1298+123+1233).
> 
> How would I do this? My thoughts were that I would do a lookup to get the 
> row numner of "Item 3" and then do a sum() with the cell range starting from 
> the row where I found "Item 3" but I'm not sure how to do that.
> 
> Thanks,
> Steve
0
Utf
1/20/2010 2:06:01 PM
I put you data in A1:B5
In G1 I type: Item3
In H1 I used formula: =ADDRESS(MATCH(G1,A:A,0),2) which returned $B$3
In I1 I used formula: =SUM(INDIRECT(H1&":B100")) which retuned 2654 as 
required

It would be easy to combine H1 and I1
What if your data began in row 3, for example? Formula in H1 returns $B$5 
and all is well
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Steve Haack" <SteveHaack@discussions.microsoft.com> wrote in message 
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
> I have a table, and I want to do a Vlookup on Column1, and when I find 
> what I
> am looking for, I want to sum column 2 from that row down to the bottom of
> the table.
>
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
>
> I want to find "Item 3" and then I want to be able to return the sum of
> (1298+123+1233).
>
> How would I do this? My thoughts were that I would do a lookup to get the
> row numner of "Item 3" and then do a sum() with the cell range starting 
> from
> the row where I found "Item 3" but I'm not sure how to do that.
>
> Thanks,
> Steve 

0
Bernard
1/20/2010 2:14:07 PM
Try this...

Data in the range A2:B6

D2 = lookup value = Item 3

=SUM(B6:INDEX(B2:B6,MATCH(D2,A2:A6,0)))

-- 
Biff
Microsoft Excel MVP


"Steve Haack" <SteveHaack@discussions.microsoft.com> wrote in message 
news:45162502-E85B-48C6-8072-7D8E073DD906@microsoft.com...
>I have a table, and I want to do a Vlookup on Column1, and when I find what 
>I
> am looking for, I want to sum column 2 from that row down to the bottom of
> the table.
>
> For example,
> Item 1     2345
> Item 2     3478
> Item 3     1298
> Item 4     123
> Item 5     1233
>
> I want to find "Item 3" and then I want to be able to return the sum of
> (1298+123+1233).
>
> How would I do this? My thoughts were that I would do a lookup to get the
> row numner of "Item 3" and then do a sum() with the cell range starting 
> from
> the row where I found "Item 3" but I'm not sure how to do that.
>
> Thanks,
> Steve 


0
T
1/20/2010 7:07:34 PM
Reply:

Similar Artilces:

How do I add the last 10 cells
If I have a column labelled, say, temperature, that is often being added to, how do I add up the last 10 cells. I want excel to do it for me automatically. I want a formula that will find the last filled-in (non blank) cell in a column and then add up the 10 entries above that. Assuming your data starts in A10, then in A9 perhaps, try the following:- =SUM(OFFSET($A$10,COUNT(A10:A9995)-10,,10)) If your data started in A15, formula would be:- =SUM(OFFSET($A$15,COUNT(A15:A10000)-10,,10)) This assumes you have no other data below this range in that column (Or at least within the COUNT range)....

find cell value
hi i have this formula that identifies the last column with data in it. =ADDRESS(35,MATCH(6.022*10^23,33:33)) this works a treat what i would like to do is us the result of this and subtract 3 column of it ie ADDRESS(35,MATCH(6.022*10^23,33:33)) = N35 i would like to have a formula that takes N35 and sub tracts 3 columns fro it giving K33 i then intend making that my range ie k12:n33 and copy the data else where to work on thanks kevin If =ADDRESS(35,MATCH(6.022*10^23,33:33)) returns $N$35 then =ADDRESS(35,MATCH(6.022*10^23,33:33)-3) will return $K$35...

'sticky' cell selection
When I select a cell it cannot be deselected and if i move my mouse it selects all cells from the original. I cannot select a singular cell anywhere else on the worksheet or select any other commands. Thus the name sticky. How do I unstick my selection to carry on using the worksheet. yippeekiay, sounds like you are in Extended Mode, EXT on in the right hand corner or the status bay, press the F8 key to get out of it -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always apprecia...

Average & blank cells
Hi guy's, I have an issue with a sheet and an average formula: I am subtracting one date from another to achieve a result (no problems) i have used the following if statement: =IF(E5-D5=0,"",E5-D5) to not show zero values as there is a long list of dates and not all the list would be fillied in all the time. My problem is that i need to create an average for these figures but need to also include the zero's in the average formula that may have the same start and end date (which results in zero but not displayed due to my if statement) without including the zeros in the cell...

16 th digit of a number in a cell gets chopped to zero
I am trying to enter a 16 digit number, like a credit card number, and no matter what kind of formatting I try, the 16th digit of the nunmber gets reset to zero! Actually, this happens for the 16th digit and beyond as well. i.e, the 17th, 18th, etc all get reset to zero no matter what digit I entered. Which begs the question whether this is a bug or is there is a upper number limit that a cell can display? If its the latter, then thats pretty dumb, since all I wish to do is just display this number and not run any math operations on it! I tried formatting it as text, or using a speci...

Hide button based on a cell value
Hello, I have been trying to write a macro that would hide a button based on a cell value (cell N20). Somehow, it doesn't work. If N20=1, the button should be visible. If not, then it should be hidden. The button is called Button 4388 and the sheet is called Results. This is what I have come up with so far but as I said it doesn't work (please don't laught, I'm a beginner!): Sub HideButton() Dim myButton As Button With ActiveSheet Set myButton = .Buttons("Button 4388") If Range("N20").Value = "1" Then ..Visible = True E...

Exporting Names and Addresses to Outlook
Hey there. From Outlook, I have tried to import data from Excel into a Contacts list...but when I go to do so, an error message in the Outlook Import/Export Wizard pops up telling me to name the range of data in Excel I'd like to import. When I go to Excel and search the help menu, nothing works.... Help help! Cindy > Hey there. Hello. > > From Outlook, I have tried to import data from Excel into > a Contacts list...but when I go to do so, an error message > in the Outlook Import/Export Wizard pops up telling me to > name the range of data in Excel I'd like...

OL 2002 Lists Multiple Names per Contact When Addressing Emails
Sorry if this question has been asked before but I have not been able to resolve or find relevant solution. When addressing an email in IMO OL 2002 its lists multiple instances per contact when the contact has more than one email address. Fax numbers are also some how liste but don't know why. IN IMO OL 2000 only the default email address was listed when addressing emails. Is there a setting somewhere or software that I could buy for OL 2002 to make it behave like 2000? The following information is an excerpt from 289467 User Profiles and Information Services http://support.micr...

Merging Info in Two Cells #2
I did get a partial response, thanks Barb, but now I need to dig into VBA and I stumble. I guess the formatting needs to be done in VBA and the easiest way is by copying each cell and pasting the values (otherwise, I am not sure if I can do partial formatting of a cell, i.e. superscripting parts of it). So the code would read: Range("c5:g5").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveCell.Characters(Start:=4, Length:=9).Font ...

Return value based on 2 items
please help am not very good at explaining things but this is my sample list 05 p5 Q5 Customer Description Cost Tom Plow 1-6 $50.00 Tom Plow 6.1-12 $100.00 Tom Plow 12 .1-18 $150.00 Tom Plow 18.1-24 $200.00 Tom Salt $25.00 Tom Shovel 1-6 $25.00 Tom ...

PDF icon in calculated cells
I have a calculated column and a PDF icon has appeared. If I delete the contents of the cells, the PDF icon remains. How can I delete the PDF icon? Right click on the icon and select cut from the popup menu. -- Regards, Tom Ogilvy "Texas Bald Eagle" <Texas Bald Eagle@discussions.microsoft.com> wrote in message news:BEBE1E3D-762B-43DE-8A60-99A86F8A8A20@microsoft.com... > I have a calculated column and a PDF icon has appeared. If I delete the > contents of the cells, the PDF icon remains. How can I delete the PDF icon? ...

E-mail addresses executing
I maintain a directory of homeowner association members in Excel. One of the columns is the e-mail address of each member. Formerly the file was in Excel 97. Moved it to a computer running Excel 2002. Now the new e-mail address entries are in executable form (excuse ignornace of Internet terms--hyperlink?), i.e. when I click on the cell, the program attempts to send an e-mail to that address. This is not what I want. I've tried formatting the cell differently, to no avail. How do I turn off this feature, so that the addresses go in as ordinary text? try copy the email address to...

Cannot add email address to contacts
When I try to add an email address to a contact, the email address will not save. I enter the email address in the proper spot in the upper right hand corner, click on either save and exit or save and the contact then only saves with the name of the contact, but no email address. I am using Outlook 2003 with XP Pro with SP 2 and the service pack for Office. My HDD crashed and I am starting over and cannot get my email addresses to save. Thanks for your help. ...

Using Indirect Cell References in a Chart
I am trying to create a 'self-sizing' chart, but don't know if I can use indirect cell references in a chart. Here's the scenario: I have a tab with data(DataTable! A2:A20) and a tab with Graphs (Graphs). I use a Max formula to determine the last row of data entered, and I've labled that formulas as 'DataTable!LastRow'. In my 'Graphs' tab, I have a cell called 'Graphs! XAxisLabel' with the formula ='Datatable!A3:A'&(DataTable! LastRow)' that displays the rows of data to be used in the chart. I want to use a formula (=Graph...

Flag row if cell values = something specific
Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

Can you change cell fonts using formula?
I have a formular: =REPT("|",(VLOOKUP($E$4,SummaryTable,6,FALSE)/200))& " " & VLOOKUP($E$4,SummaryTable,6,FALSE) but I need to change the font of the 2nd "VLOOKUP($E$4,SummaryTable,6,FALSE)" section. Is this possible to do? No. but you can change to text and do whatever is desired. However, you no longer have a formula. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:F48837E4-7014-421C-8958-FC40C5AB3C0E@microsoft.com... >I hav...

Vlookup(s) problem
I'm hoping someone can help me out. I'm trying to automate the creation of a vendor report that lists al vendor sales. Basically I need a formula that, based on a unique vendo number, will: - check colomn A for the vendor number match - then check column B to see if there is a buyer number (which mean that the item is sold) - then dumps then 'nth' occurance of the value / text from the column specify (8) I'm currrently using the formula: {=IF(Catalog!B2:B428 0,INDEX(VendorReportData,SMALL(IF(VendorReportData =$N$13,ROW(Catalog!$A$2:$A$428) ROW(Catalog!$A$2)+1,ROW(Cat...

Inserting Outlook Addresses into a Word Doc
When you use your Outlook 2002 Contacts folder to insert a single address into a Word document, the only way a Contact shows up in the Contact list is if you have made an entry in either the Contact forms Fax or E-mail fields. Why? The problem is that if you only have a Fax number and no e-mail listed for the Contact, the Contact's name is inserted into the Word document with the words "(Business Fax)" or "(Home Fax)" attached to the Full Name. How can you eliminate the insertion of these words? What happens if your Contact has neither a Fax number nor an e...

Unwanted e-mail addresses
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: pop E-mail addresses from spam emails keep getting added to my address book, but when I open it to delete them, I can't find them. I know they're there because they come up when I try to address an email. Where are they kept and how can I remove them? On 1/7/10 2:58 PM, in article 59bb046c.-1@webcrossing.JaKIaxP2ac0, "Texile@officeformac.com" <Texile@officeformac.com> wrote: > E-mail addresses from spam emails keep getting added to my address book, but > when I o...

Delete cells with content that don't contain the =?UTF-8?B?wqMgc3ltYm9s?=
I have a very large messy excel file that contains some data I want to isolate. The other data is unneeded and basically in the way. I want to delete/clear all the other cells that do not contain the ? pound symbol. This way I will be left with just the pricing info I need. All help appreciated On Fri, 23 Mar 2012 12:46:11 GMT, Gary N <gary.neill@allstate.com> wrote: >I want to delete/clear all the other cells that do not contain the ? pound symbol. "Be careful what you wish for" This can be done with a VBA Macro: To enter this Macro (Sub), <alt-F11> opens t...

DateDiff("yyyy",[Anniversary],Now()) returns rounded number
I am using DateDiff("yyyy",[Anniversary],Now()) in a query to return the number of years between two dates. Since it is an Anniversary calculation, the result needs to NOT round up. Currently, when the result is 11.73, it rounds up to 12 when I really need it to stay at 11 until the anniversary date is reached. Thanks in advance for any help. First of all DateDiff returns integer values and never fractional values. DateDiff returns the number of transitions that occur. So Dec 31 2008 to Jan 1 2009 will return 1 year even though only one day has elapsed. The number...

Vlookup problem
I am having a problem with a vlookup fromula returning an #N/A value when using a cell with another vlookup formula as the lookup_ value. I created this same spreadsheet a year ago and it worked fine. I even opened the old one and I can't see any differences at all. I'd appreciate any help anyone can give. -- tpocccfo We'd be happy to help, but you need to meet us halfway. What's the formula? What data is providing the #N/A result? Regards, Fred "tpocccfo" <tpocccfo@discussions.microsoft.com> wrote in message news:6EFE978D-A5DE-4DCC-8B4...

Enter date automatically in cell
Hi i would like my system date to be inputted into an excel cell, how do i go about doing this. Cheers NO Hi Mo CTRL-; will add the date in the activecell -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "MO" <anonymous@discussions.microsoft.com> wrote in message news:77a201c3e748$c4caea40$a601280a@phx.gbl... > Hi i would like my system date to be inputted into an > excel cell, how do i go about doing this. > > Cheers > > NO Enter =Today() in the cell >-----Original Message----- >Hi i would like my system date to b...

Addresses and Contacts
I think this is the most confusing part of Outlook. Iam trying to move everything over to a new laptop. I have copied all my pst files. If i try and create a new email now, there are no addresses to select from....But if I go into the contacts within Outlook they are there. What do I have to do to transfer my contacts over so I can use them??? How do the contacts work in Outlook? I have my pst files in My documents, so that I can back them up, is it possible to path other things to my docs as well? Iam using Outlook 2003 SP2. Cheers, Scott V if you have Contacts in the Contacts...

email addresses don't pdf correctly in Publisher 2007
When I highlight someone's name and hyperlink it to an email address, Publisher automatically adds mailto: at the beginning of the address and the link works fine. But when I pdf it, whether I use the Publisher print as PDF, or use the print menu and choose Adobe pdf, or go from Adobe and create a pdf, the link on the email address ends up as mailto:mailto:emailaddress, i.e., 2 mailto:'s show up. So then when you click on it, it opens up my email program and puts one of "mailto:" in front of the address instead of just putting the address there and the email won't ...