# VLOOKUP and DGET to find a value with multiple criterion

jaybird2307
Guest
Posts: n/a

 19th Jun 2006

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.

--
jaybird2307
------------------------------------------------------------------------
jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577

Biff
Guest
Posts: n/a

 19th Jun 2006
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

B2:B6 = year numbers

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.
>
>
>
> --
> jaybird2307
> ------------------------------------------------------------------------
> jaybird2307's Profile:
> http://www.excelforum.com/member.php...o&userid=35577
>

jaybird2307
Guest
Posts: n/a

 20th Jun 2006

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

Any help would be appreciated.

+-------------------------------------------------------------------+
|Filename: Example Data.zip |
+-------------------------------------------------------------------+

--
jaybird2307
------------------------------------------------------------------------
jaybird2307's Profile: http://www.excelforum.com/member.php...o&userid=35577

Biff
Guest
Posts: n/a

 20th Jun 2006
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)...
>
>
> 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
>
> Any help would be appreciated.
>
>
> +-------------------------------------------------------------------+
> |Filename: Example Data.zip |
> +-------------------------------------------------------------------+
>
> --
> jaybird2307
> ------------------------------------------------------------------------
> jaybird2307's Profile:
> http://www.excelforum.com/member.php...o&userid=35577
>

lostinformulas
Guest
Posts: n/a

 20th Jun 2006

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

Biff
Guest
Posts: n/a

 20th Jun 2006
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

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
>

jaybird2307
Guest
Posts: n/a

 21st Jun 2006

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

Biff
Guest
Posts: n/a

 21st Jun 2006
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
>

jaybird2307
Guest
Posts: n/a

 28th Jun 2006

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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Chiann Looker Microsoft Excel Worksheet Functions 1 9th Mar 2010 03:36 AM =?Utf-8?B?RWRkeSBTdGFu?= Microsoft Excel Worksheet Functions 4 1st Sep 2007 10:22 PM Sam via OfficeKB.com Microsoft Excel Worksheet Functions 4 18th Sep 2006 11:20 PM Sam via OfficeKB.com Microsoft Excel Worksheet Functions 0 18th Sep 2006 02:13 PM =?Utf-8?B?QWRlbGxh?= Microsoft Excel Worksheet Functions 4 13th Oct 2004 01:49 PM

Features