INDEX result with date criteria & skip empty cell

[phithack]

[TABLE="width: 350"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"] G [/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]FROM[/TD]
[TD="align: center"]TO[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]01-03-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD]18,900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]10-03-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD]18,500[/TD]
[TD][/TD]
[TD]DATE : [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]29-03-18[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Z[/TD]
[TD]18,900[/TD]
[TD][/TD]
[TD]FROM :[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]11-04-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD]TO :[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]18-04-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD]19,500[/TD]
[TD][/TD]
[TD]RESULT :[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]18-04-18[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Z[/TD]
[TD]19,800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]26-04-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]27-04-18[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]27-04-18[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

HI, I want to extract result (G6) that would skip empty cell if I input data DATE (G3), FROM (G4), TO (G5) for examples:

EX1:

DATE : 26-04-18
FROM : A
TO : Z
RESULT : 19,500 (D6)

EX2:

DATE : 12-04-18
FROM : A
TO : Z
RESULT : 18,500 (D3)


Sorry for my bad English
Thank you so much

 

[Aladin Akyurek]

In G6 control+shift+enter, not just enter:

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,$D$2:$D$10,"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$D$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER($D$2:$D$10),IF($A$2:$A$10<=G3,ROW($D$2:$D$10)-ROW($D$2)+1)))))),"not available")

 

[phithack]

In G6 control+shift+enter, not just enter:

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,$D$2:$D$10,"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$D$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER($D$2:$D$10),IF($A$2:$A$10<=G3,ROW($D$2:$D$10)-ROW($D$2)+1)))))),"not available")

Thank you so much Aladin
That works perfectly

Another question if you don't mind.
How if I have 2 or more price columns ex. Price 1 (Column D) & Price 2 (Column E) etc.
What should I do in the Index formula for [column_num]?
Or should I change the entire formula?

Sorry for asking so many question

 

[Aladin Akyurek]

Thank you so much Aladin
That works perfectly

You are welcome.

Another question if you don't mind.
How if I have 2 or more price columns ex. Price 1 (Column D) & Price 2 (Column E) etc.
What should I do in the Index formula for [column_num]?
Or should I change the entire formula?

Sorry for asking so many question

If you want to run the formula on a certain price range...

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,INDEX($D$2:$E$10,0,kappa),"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$E$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER($D$2:$D$10),IF($A$2:$A$10<=G3,ROW($A$2:$E$10)-ROW($A$2)+1))))),kappa),"not available")

When kappa is set to 1, the formula picks out the range in D; When kappa = 2, it picks out the range in E.

 

[Peter_SSs]

Welcome to the MrExcel board!

In relation to your first question, you could also consider this standard-entry formula

=IFERROR(INDEX(D2:D10,AGGREGATE(14,6,(ROW(D2:D10)-ROW(D2)+1)/((A2:A10<=G3)*(B2:B10=G4)*(C2:C10=G5)*(D2:D10<>"")),1)),"not available")

 

[phithack]

You are welcome.



If you want to run the formula on a certain price range...

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,INDEX($D$2:$E$10,0,kappa),"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$E$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER($D$2:$D$10),IF($A$2:$A$10<=G3,ROW($A$2:$E$10)-ROW($A$2)+1))))),kappa),"not available")

When kappa is set to 1, the formula picks out the range in D; When kappa = 2, it picks out the range in E.

I tried the formula but when kappa = 2, it doesn't work like kappa = 1

The data in Column E (Price 2) :

E2 = 18,700
E4 = 17,000



When I input :

DATE : 11-04-18 | Price 2
FROM : A
TO : Z

The result is supposed to be 18,700 but it came out 0


Btw, I change kappa with MATCH(H3;D1:E1;0), H3 is the cell Price 1 or Price 2

 

[phithack]

Welcome to the MrExcel board!

In relation to your first question, you could also consider this standard-entry formula

=IFERROR(INDEX(D2:D10,AGGREGATE(14,6,(ROW(D2:D10)-ROW(D2)+1)/((A2:A10<=G3)*(B2:B10=G4)*(C2:C10=G5)*(D2:D10<>"")),1)),"not available")

Thank you for your reply.
But, it doesn't work.
Everything came out "not available"

 

[Aladin Akyurek]

I tried the formula but when kappa = 2, it doesn't work like kappa = 1

The data in Column E (Price 2) :

E2 = 18,700
E4 = 17,000



When I input :

DATE : 11-04-18 | Price 2
FROM : A
TO : Z

The result is supposed to be 18,700 but it came out 0


Btw, I change kappa with MATCH(H3;D1:E1;0), H3 is the cell Price 1 or Price 2

We need to edit one term that I have accidentally skipped...

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,INDEX($D$2:$E$10,0,kappa),"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$E$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER(INDEX($D$2:$E$10,0,kappa)),IF($A$2:$A$10<=G3,ROW($A$2:$E$10)-ROW($A$2)+1))))),kappa),"not available")

 

[Peter_SSs]

Thank you for your reply.
But, it doesn't work.
Everything came out "not available"

Hmm, I tested with each of your examples (& some others) & my formula produced identical results to Aladin's. I've shown it below working with one of the examples.

What version of Excel are you using?

Excel Workbook
	A	B	C	D	E	F	G
1	DATE	FROM	TO	PRICE
2	1/03/2018	A	Z	18,900
3	10/03/2018	A	Z	18,500		DATE :	12/04/2018
4	29/03/2018	B	Z	18,900		FROM :	A
5	11/04/2018	A	Z			TO :	Z
6	18/04/2018	A	Z	19,500		RESULT :	18500
7	18/04/2018	B	Z	19,800
8	26/04/2018	A	Z
9	27/04/2018	A	Y
10	27/04/2018	B	Y
Skip Empty

 

[phithack]

We need to edit one term that I have accidentally skipped...

=IF(COUNTIFS($B$2:$B$10,G4,$C$2:$C$10,G5,INDEX($D$2:$E$10,0,kappa),"<>",$A$2:$A$10,"<="&G3),INDEX($D$2:$E$10,MAX(IF($B$2:$B$10=G4,IF($C$2:$C$10=G5,IF(ISNUMBER(INDEX($D$2:$E$10,0,kappa)),IF($A$2:$A$10<=G3,ROW($A$2:$E$10)-ROW($A$2)+1))))),kappa),"not available")

That works..
Now my problem solved..
Thank you so much Aladin..

Hmm, I tested with each of your examples (& some others) & my formula produced identical results to Aladin's. I've shown it below working with one of the examples.

What version of Excel are you using?

Skip Empty

	A	B	C	D	E	F	G
	DATE :
	FROM :
		TO :
	RESULT :

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:85px;"><col style="width:59px;"><col style="width:38px;"><col style="width:60px;"><col style="width:18px;"><col style="width:79px;"><col style="width:85px;"></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="align: right"]DATE[/TD]
[TD="align: center"]FROM[/TD]
[TD="align: center"]TO[/TD]
[TD="align: right"]PRICE[/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"]1/03/2018[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD="align: right"]18,900[/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"]10/03/2018[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD="align: right"]18,500[/TD]

[TD="align: center"]12/04/2018[/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"]29/03/2018[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Z[/TD]
[TD="align: right"]18,900[/TD]

[TD="align: center"]A[/TD]

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

[TD="align: center"]Z[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]18/04/2018[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/TD]
[TD="align: right"]19,500[/TD]

[TD="align: center"]18500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]18/04/2018[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Z[/TD]
[TD="align: right"]19,800[/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"]26/04/2018[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Z[/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"]27/04/2018[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Y[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]27/04/2018[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Y[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G6 =IFERROR(INDEX(D2:D10,AGGREGATE(14,6,(ROW(D2:D10)-ROW(D2)+1)/((A2:A10<=G3)*(B2:B10=G4)*(C2:C10=G5)*(D2:D10<>"")),1)),"not available") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sorry Peter..
My bad..
I'm using Excel 2007..

Thank you for trying to help me..