VLOOKUP and DGET to find a value with multiple criterion

[jaybird2307]

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.

 

[Biff]

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]...

 

[jaybird2307]

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 |
+-------------------------------------------------------------------+

 

[Biff]

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

 

[lostinformulas]

Biff

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

I like this formula it has great possiblities.

Thank

 

[Biff]

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"

 

[jaybird2307]

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

 

[Biff]

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

 

[jaybird2307]

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.