IF and Index Match

[jamesmev]

I am trying to avoid using vlookup... That might be the most over used statement in Excel world.

I have a sheet where I need the result if two criteria are met.
For simplicity I'll try to explain below

A B C
NAME Date Total



So if you have on sheet 1 "Name" and it falls within "Date" it provides "total"

Any help would be appreciated... This might be one of those .... DUH formula's but - my brain is broken.

 

[duggie33]

Are you looking to return a single value, sum multiple values that meet the name and date requirements, or something else?

I personally think vlookup is useful for certain situations...it's simple.

 

[AhoyNC]

Maybe something like one of the below formulas (it would help to post an example with the expected resulted).

The INDEX AGGREGATE formula in C8 will work in Excel 2010 or later.
If you have an earlier ver. then maybe something like the formula in C9 which must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	NAME	Date	Total
2	Mary Smith	2/5/2019	45
3	Becky Jones	3/5/2019	56
4	Sam Spade	3/1/2019	68
5
6
7	Lookup
8	Becky Jones	3/5/2019	56
9			56
Sheet

 

[jamesmev]

Maybe something like one of the below formulas (it would help to post an example with the expected resulted).

The INDEX AGGREGATE formula in C8 will work in Excel 2010 or later.
If you have an earlier ver. then maybe something like the formula in C9 which must be entered with CTRL-SHIFT-ENTER.

	A	B	C
Mary Smith
Becky Jones
Sam Spade
Becky Jones

<colgroup><col style="width:30px; "><col style="width:90px;"><col style="width:86px;"><col style="width:76px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]NAME[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Total[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]2/5/2019[/TD]
[TD="align: right"]45[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]3/5/2019[/TD]
[TD="align: right"]56[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "]Lookup[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]3/5/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]56[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]56[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C8 =INDEX($C$2:$C$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-ROW($A$2)+1)/(($A$2:$A$4=$A$8)*($B$2:$B$4=$B$8)),1)) C9 {=INDEX($C$2:$C$4,MATCH(1,($A$2:$A$4=A8)*($B$2:$B$4=$B$8),0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The Aggregate works perfectly. I am running into an issue that the data is a Pivot.. Is there a way to combat that? I am getting a NUM error?
Any Ideas?

 

[jamesmev]

It appears to be the reference to a secondary sheet. . . The formula lives on Sheet 1 - - - the data lives on Sheet 2. When I reference the 2nd sheet is where I'm getting the issue. The Pivot isn't creating the problem.

 

[AhoyNC]

See if this example helps using 2 sheets.

Excel Workbook
	A	B	C
1	Lookup
2	Becky Jones	3/5/2019	56
Sheet1

Excel Workbook
	A	B	C
1	NAME	Date	Total
2	Mary Smith	2/5/2019	45
3	Becky Jones	3/5/2019	56
4	Sam Spade	3/1/2019	68
Sheet2

 

[jamesmev]

Thank you. I appreciate it. I believe this has been resolved. Great help!

 

[AhoyNC]

You're welcome. Thanks for the feedback.