| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup adding more than one instance
Good Evening
All - I have searched this group high and low for an answer and I can't seem to grasp anything to create what I'm after.. Though I totally appreciate all the information Here it goes... I have 2 sheets in Excel. The first sheet in Excel lists a master financial statement description list of the balance sheet, a balance sheet if you will First Sheet - Master Balance Sheet cash accounts receivable prepaid assets I assigned a numerical value for each Cash = 1 Accounts Receivable = 2 prepaid assets = 3 All the way from "1" = Cash to "41" = Total Liabilites and Owners Equity Second Sheet - Balance Sheet has different descriptions but in the end only 41 different numbers will be picked up from sheet 2 This sheet contains a financial statement with Cash = "1" 40,000 Money Market = "1" 20,000 Accounts Receivable = "2" 20,000 Prepaid Assets = "3" 10,000 All the way down to "41" = Total Liabilites and Owners Equity I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000 because VLOOKUP will only pick up one instance... How do I get Excel to pick up all instances of 1 all instances of 2 etc... without using SUMIF? I tried sumif, but that didn't seem to work right ... Thanks, ExcelUser777 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Hi!
So, what do you want to do, list all amounts that coresspond to a certain number category? Lookup all instances of 1 and return: 40,000 20,000 Biff "ExcelUser777" wrote in message oups.com... Good Evening All - I have searched this group high and low for an answer and I can't seem to grasp anything to create what I'm after.. Though I totally appreciate all the information Here it goes... I have 2 sheets in Excel. The first sheet in Excel lists a master financial statement description list of the balance sheet, a balance sheet if you will First Sheet - Master Balance Sheet cash accounts receivable prepaid assets I assigned a numerical value for each Cash = 1 Accounts Receivable = 2 prepaid assets = 3 All the way from "1" = Cash to "41" = Total Liabilites and Owners Equity Second Sheet - Balance Sheet has different descriptions but in the end only 41 different numbers will be picked up from sheet 2 This sheet contains a financial statement with Cash = "1" 40,000 Money Market = "1" 20,000 Accounts Receivable = "2" 20,000 Prepaid Assets = "3" 10,000 All the way down to "41" = Total Liabilites and Owners Equity I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000 because VLOOKUP will only pick up one instance... How do I get Excel to pick up all instances of 1 all instances of 2 etc... without using SUMIF? I tried sumif, but that didn't seem to work right ... Thanks, ExcelUser777 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Wow what a quick response
Hi Biff Yes I would like to be able to do what you mentioned. On the second sheet I might have instances of 3 threes etc. 4 fours etc. etc... Vlookup is limited for this type of issue? My goal is to set up a Macro to handle the description grouping.. but I am creating this manual first and then automate later.. I actually just got the sumif to work for me, but I read that Sumif can be faulty when you are linking files and I use alot of linked files. I noticed alot of posting using arrays, but these solutions seemed to be if people wanted to multiply figures in one column 1 by column 2 Thanks, ExcelUser777 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Ok, I'm still not 100% sure of what you want to do but have a look at this
sample file: http://s63.yousendit.com/d.aspx?id=1...00K1V3DOANEYN6 Is that what you had in mind? The formula that returns the category amounts is an array formula. It must be copied to enough cells so that all the coressponding amounts are returned. Since the number of amounts vary by category you need to copy the formula to enough cells that will cover the maximum number of amounts for ANY category. Biff "ExcelUser777" wrote in message oups.com... Wow what a quick response Hi Biff Yes I would like to be able to do what you mentioned. On the second sheet I might have instances of 3 threes etc. 4 fours etc. etc... Vlookup is limited for this type of issue? My goal is to set up a Macro to handle the description grouping.. but I am creating this manual first and then automate later.. I actually just got the sumif to work for me, but I read that Sumif can be faulty when you are linking files and I use alot of linked files. I noticed alot of posting using arrays, but these solutions seemed to be if people wanted to multiply figures in one column 1 by column 2 Thanks, ExcelUser777 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
That's great.
€œBiff€ç¼–写： Hi! So, what do you want to do, list all amounts that coresspond to a certain number category? Lookup all instances of 1 and return: 40,000 20,000 Biff "ExcelUser777" wrote in message oups.com... Good Evening All - I have searched this group high and low for an answer and I can't seem to grasp anything to create what I'm after.. Though I totally appreciate all the information Here it goes... I have 2 sheets in Excel. The first sheet in Excel lists a master financial statement description list of the balance sheet, a balance sheet if you will First Sheet - Master Balance Sheet cash accounts receivable prepaid assets I assigned a numerical value for each Cash = 1 Accounts Receivable = 2 prepaid assets = 3 All the way from "1" = Cash to "41" = Total Liabilites and Owners Equity Second Sheet - Balance Sheet has different descriptions but in the end only 41 different numbers will be picked up from sheet 2 This sheet contains a financial statement with Cash = "1" 40,000 Money Market = "1" 20,000 Accounts Receivable = "2" 20,000 Prepaid Assets = "3" 10,000 All the way down to "41" = Total Liabilites and Owners Equity I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000 because VLOOKUP will only pick up one instance... How do I get Excel to pick up all instances of 1 all instances of 2 etc... without using SUMIF? I tried sumif, but that didn't seem to work right ... Thanks, ExcelUser777 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Ooops!
Caught a bug! Change this portion of the array formula: =IF(ROWS($1:1)<=B$2 To: =IF(AND(ROWS($1:1)<=B$2,B$2<"") This modification will account for cell B2 being empty. (which currently causes an error) Biff "Biff" wrote in message ... Ok, I'm still not 100% sure of what you want to do but have a look at this sample file: http://s63.yousendit.com/d.aspx?id=1...00K1V3DOANEYN6 Is that what you had in mind? The formula that returns the category amounts is an array formula. It must be copied to enough cells so that all the coressponding amounts are returned. Since the number of amounts vary by category you need to copy the formula to enough cells that will cover the maximum number of amounts for ANY category. Biff "ExcelUser777" wrote in message oups.com... Wow what a quick response Hi Biff Yes I would like to be able to do what you mentioned. On the second sheet I might have instances of 3 threes etc. 4 fours etc. etc... Vlookup is limited for this type of issue? My goal is to set up a Macro to handle the description grouping.. but I am creating this manual first and then automate later.. I actually just got the sumif to work for me, but I read that Sumif can be faulty when you are linking files and I use alot of linked files. I noticed alot of posting using arrays, but these solutions seemed to be if people wanted to multiply figures in one column 1 by column 2 Thanks, ExcelUser777 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Biff,
This is excellent...when I finally get it working The Array doesn't seem to work when you choose 5 from the drop down list. Thanks, ExcelUser777 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
Biff,
Thanks a bunch I love how I can choose the number from your Excel Spreadsheet. What I want to have happen is to show all instances of "1" summed up into one cell, all instances of "2" summed up into one cell, what you did is great Sumif works...I just don't know if that is the best way do it.... |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
The Array doesn't seem to work when you choose 5 from the drop down
list. It works for me. Biff "ExcelUser777" wrote in message ups.com... Biff, This is excellent...when I finally get it working The Array doesn't seem to work when you choose 5 from the drop down list. Thanks, ExcelUser777 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup adding more than one instance
You're welcome. Thanks for the feedback!
Biff "ExcelUser777" wrote in message ups.com... Biff, Thanks a bunch I love how I can choose the number from your Excel Spreadsheet. What I want to have happen is to show all instances of "1" summed up into one cell, all instances of "2" summed up into one cell, what you did is great Sumif works...I just don't know if that is the best way do it.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup - effect of adding columns | Excel Discussion (Misc queries) | |||
| Adding two columns with vlookup | Excel Worksheet Functions | |||
| Adding using Vlookup | Excel Discussion (Misc queries) | |||
| Vlookup to find Second INstance | Excel Worksheet Functions | |||
| Adding a Macro to a VLookup Function | Excel Discussion (Misc queries) | |||
Linear Mode
