INDEX_MATCH or VLOOKUP with multiple criteria?

[Royzer]

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

Thanks

+-------------------------------------------------------------------
|Filename: Multiple criteria INDEX_MATCH or VLOOKUP file.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=394
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[Claus Busch]

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

 

[Claus Busch]

Hi,

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

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$200&$J$10:$J$200,0))


Regards
Claus Busch

 

[Royzer]

Hi,

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

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


Regards
Claus Busch

Thank you, Claus. I had tried to mark the thread as "SOLVED" prior to t
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 work
for me:


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


Thank you again, Claus

+-------------------------------------------------------------------
+-------------------------------------------------------------------