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

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

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

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(Database!$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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

Biff

I have a question in your formula what does the "--" stand for.

I like this formula it has great possiblities.

Thank

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55341

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"
<(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> 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
>

Biff -

Yes, I tried your suggestion. I couldn't get past the idea that
needed to use a combination of VLOOKUP and DGET/DSUM.

I've never used the SUMPRODUCT or MATCH functions and didn't understan
it. But, I tried it and it worked. I'll study Excel's hel
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

--
jaybird230
-----------------------------------------------------------------------
jaybird2307's Profile: http://www.excelforum.com/member.php...fo&userid=3557
View this thread: http://www.excelforum.com/showthread.php?threadid=55341

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" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> 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
>

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