INDEX_MATCH or VLOOKUP with multiple criteria?
 

Hi. I have searched and tried everything I can find about using multiple criteria to return values, but I have not found anything that will work for my project. Basically, I have multiple sheets and each sheet has a unique company number at the top and then rows with dates below it. I need these sheets to look at a a different sheet that has date rows for several company numbers and dollar amounts. Each of the company sheets needs to be able to bring in the correct dollar amount for that company and date. I've attached a workbook to show what I'd like to be able to do. I'd really appreciate any help you could give me.

Thanks!

SOLVED

Edited to add solution:

=SUMIFS($K:$K,$I:$I,$B20,$J:$J,"*"&C$18)

INDEX_MATCH or VLOOKUP with multiple criteria?
 

Hi,

Am Tue, 29 May 2012 18:45:03 +0000 schrieb Royzer:

Hi. I have searched and tried everything I can find about using multiple
criteria to return values, but I have not found anything that will work
for my project. Basically, I have multiple sheets and each sheet has a
unique company number at the top and then rows with dates below it. I
need these sheets to look at a a different sheet that has date rows for
several company numbers and dollar amounts. Each of the company sheets
needs to be able to bring in the correct dollar amount for that company
and date. I've attached a workbook to show what I'd like to be able to
do. I'd really appreciate any help you could give me.

in C20 try:
=INDEX($K10:$K200,MATCH($B20&"*"&C$18,$I$10:$I$200 &$J$10:$J$200,0))
and drag to E22


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

INDEX_MATCH or VLOOKUP with multiple criteria?
 

Hi,

Am Tue, 29 May 2012 22:00:25 +0200 schrieb Claus Busch:

in C20 try:
=INDEX($K10:$K200,MATCH($B20&"*"&C$18,$I$10:$I$200 &$J$10:$J$200,0))
and drag to E22

it is an array formula to enter with
CTRL+Shift+Enter

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

INDEX_MATCH or VLOOKUP with multiple criteria?
 

Hi,

Am Tue, 29 May 2012 22:03:31 +0200 schrieb Claus Busch:

in C20 try:
=INDEX($K10:$K200,MATCH($B20&"*"&C$18,$I$10:$I$200 &$J$10:$J$200,0))
and drag to E22

it is an array formula to enter with
CTRL+Shift+Enter

sorry, have a typo in it. K10:K200 must also be absolute:
=INDEX($K$10:$K$200,MATCH($B20&"*"&C$18,$I$10:$I$2 00&$J$10:$J$200,0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you, Claus. I had tried to mark the thread as "SOLVED" prior to to your post, but cannot find a way to do it through the forum tools. Here's the formula I was given before you posted, which seems to works for me:


=SUMIFS($K:$K,$I:$I,$B20,$J:$J,"*"&C$18)


Thank you again, Claus.