Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
I have a range of data where I need to find the value in a column by narrowing down with two different criteria. These are my column titles: Account Number Year January February March etc. I need to lookup down the account number column and return the value for March where the Year is 2005. In other words, on another spreadsheet, I have a list of accounts and I'm trying to search my data range and find the March value where the year is 2005. I'm not sure how to combine DGET, VLOOKUP, MATCH, and/or INDEX to accomplish this. Thanks for your help. -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Hi!
Is the value you looking for TEXT or numeric? If it's numeric and the criteria combinations are unique: A1 = header = Account Number A2:A6 = account numbers B1 = header = Year B2:B6 = year numbers C1:E1 = headers = January,February,March C2:E6 = some numeric data =SUMPRODUCT(--(A2:A6=2),--(B2:B6=2005),INDEX(C2:E6,,MATCH("March",C1:E1,0))) This formula will return the value for acct # 2 for the year 2005 for the month of March. Biff "jaybird2307" wrote in message ... I have a range of data where I need to find the value in a column by narrowing down with two different criteria. These are my column titles: Account Number Year January February March etc. I need to lookup down the account number column and return the value for March where the Year is 2005. In other words, on another spreadsheet, I have a list of accounts and I'm trying to search my data range and find the March value where the year is 2005. I'm not sure how to combine DGET, VLOOKUP, MATCH, and/or INDEX to accomplish this. Thanks for your help. -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Thanks for your response. What I need to do is lookup an account number in my database, return the value for a specified column where the year equals a certain year. For example, I want the value for an account number for March where the year equal 2006. I'm attaching an example workbook. The first tab is my "report" (the worksheet where I want to get my desired result). The second tab is an example database where I want to pull my data from. The third tab is where I might put some "criteria" if using a database function (DGET or DSUM). The account number is not a fixed item that I can put under a criteria column heading. That's my problem. Only the year would work for me under a criteria heading. Any help would be appreciated. +-------------------------------------------------------------------+ |Filename: Example Data.zip | |Download: http://www.excelforum.com/attachment.php?postid=4909 | +-------------------------------------------------------------------+ -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Hi!
Did you try my suggestion? The third tab is where I might put some "criteria" if using a database function (DGET or DSUM). Don't get "hung up" on the idea that you HAVE to use D functions for this. The fact is, D functions are obsolete and their functionality can be more easily replicated using other functions like Sumproduct. Based on your sample file, this formula entered in Report B4 returns the correct value: =SUMPRODUCT(--(Database!$A$5:$A$27=$A4),--(Database!$B$5:$B$27=Worksheet!$A$4),INDEX(Databas e!$C$5:$N$27,,MATCH(B$3,Database!$C$4:$N$4,0))) If that's not what you want then I don't understand what you want. Biff "jaybird2307" wrote in message ... Thanks for your response. What I need to do is lookup an account number in my database, return the value for a specified column where the year equals a certain year. For example, I want the value for an account number for March where the year equal 2006. I'm attaching an example workbook. The first tab is my "report" (the worksheet where I want to get my desired result). The second tab is an example database where I want to pull my data from. The third tab is where I might put some "criteria" if using a database function (DGET or DSUM). The account number is not a fixed item that I can put under a criteria column heading. That's my problem. Only the year would work for me under a criteria heading. Any help would be appreciated. +-------------------------------------------------------------------+ |Filename: Example Data.zip | |Download: http://www.excelforum.com/attachment.php?postid=4909 | +-------------------------------------------------------------------+ -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Biff I have a question in your formula what does the "--" stand for. I like this formula it has great possiblities. Thanks -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Hi!
These logical expressions: (Database!$A$5:$A$27=$A4) (Database!$B$5:$B$27=Worksheet!$A$4) will return arrays of TRUEs or FALSEs Sort of like this: {TRUE;TRUE;FALSE;TRUE;FALSE} {FALSE;FALSE;TRUE;TRUE;FALSE} The "--" converts those to 1 or 0, TRUE = 1, FALSE = 0 {1;1;0;1;0} {0;0;1;1;0} Then those arrays are multiplied together along with the numbers from the result of this expression: INDEX(Database!$C$5:$N$27,,MATCH(B$3,Database!$C$4 :$N$4,0)) That would look something like this: 1 * 0 * 10 = 0 1 * 0 * 22 = 0 0 * 1 * 11 = 0 1 * 1 * 20 = 20 0 * 0 * 15 = 0 =SUMPRODUCT({0;0;0;20;0}) = 20 See these sites for more info: http://xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html Biff "lostinformulas" <lostinformulas.29pzg0_1150839004.3205@excelforu m-nospam.com wrote in message news:lostinformulas.29pzg0_1150839004.3205@excelfo rum-nospam.com... Biff I have a question in your formula what does the "--" stand for. I like this formula it has great possiblities. Thanks -- lostinformulas ------------------------------------------------------------------------ lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Biff - Yes, I tried your suggestion. I couldn't get past the idea that I needed to use a combination of VLOOKUP and DGET/DSUM. I've never used the SUMPRODUCT or MATCH functions and didn't understand it. But, I tried it and it worked. I'll study Excel's help documentation for SUMPRODUCT and MATCH so that I can understand "why" it worked and will be able to use it in the future. Thanks for the help. -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
You're welcome. Thanks for the feedback!
I'll study Excel's help documentation for SUMPRODUCT You'll find it quite sparse and it doesn't even scratch the surface on how versatile this function is. See this for a definitive guide: http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "jaybird2307" wrote in message ... Biff - Yes, I tried your suggestion. I couldn't get past the idea that I needed to use a combination of VLOOKUP and DGET/DSUM. I've never used the SUMPRODUCT or MATCH functions and didn't understand it. But, I tried it and it worked. I'll study Excel's help documentation for SUMPRODUCT and MATCH so that I can understand "why" it worked and will be able to use it in the future. Thanks for the help. -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP and DGET to find a value with multiple criterion
Biff - I'm back to this problem again. The SUMPRODUCT function is only working partially for me. As I understand it, my ranges have to be the same size in order for this to work. Let me try an example to illustrate: I have two worksheets. On the first is a list of all customer numbers. On the second is a list of sales $ (by customer number). The second worksheet does not contain a record for every customer number, and, some customer numbers will have more than one record (because one of the columns on the second worksheet is "year"). So, I have a list of all my customers (on the first worksheet), and I want to sum up information from my second sheet, by customer, where the "year" value is equal to X. If a customer did not have sales, then I want to return the value 0. What the SUMPRODUCT formula is doing for me now is working, but only on the rows of my first worksheet that are within the row range of my second worksheet. (Worksheet #1 has 1240 customer numbers listed. Worksheet #2 has only 1032 records. Worksheet #1 SUMPRODUCT formula works up through row #1032. Formula does not work for rows 1033 through 1240.) Hope you can help. Thx. -- jaybird2307 ------------------------------------------------------------------------ jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577 View this thread: http://www.excelforum.com/showthread...hreadid=553411 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DGet vs VLookup | Excel Worksheet Functions | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions | |||
At my wit's end! Use DGET or VLOOKUP to select data???? | Excel Worksheet Functions |