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.

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.

Thanks

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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 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.

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.