Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
SOLVED
Quote:
=SUMIFS($K:$K,$I:$I,$B20,$J:$J,"*"&C$18) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
=SUMIFS($K:$K,$I:$I,$B20,$J:$J,"*"&C$18) Thank you again, Claus. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup multiple criteria multiple occurrences sum values | Excel Worksheet Functions | |||
Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
Vlookup for multiple criteria | Excel Worksheet Functions | |||
Vlookup with multiple criteria | Excel Worksheet Functions |