INDEX result with date criteria & skip empty cell

P

phithack

New Member
Joined
May 21, 2018
Messages
6
[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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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")
 
Upvote 0
Aladin Akyurek said:
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")
Click to expand...

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 :)
 
Upvote 0
phithack said:
Thank you so much Aladin
That works perfectly :)
Click to expand...

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 :)
Click to expand...

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.
 
Upvote 0
Welcome to the MrExcel board!

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

Code: 
=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")
 
Last edited:
Upvote 0
Aladin Akyurek said:
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.
Click to expand...


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
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

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

Code: 
=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")
Click to expand...

Thank you for your reply.
But, it doesn't work.
Everything came out "not available"
 
Upvote 0
phithack said:
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
Click to expand...

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")
 
Last edited:
Upvote 0
phithack said:
Thank you for your reply.
But, it doesn't work.
Everything came out "not available"
Click to expand...
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
ABCDEFG
1DATEFROMTOPRICE
21/03/2018AZ18,900
310/03/2018AZ18,500DATE :12/04/2018
429/03/2018BZ18,900FROM :A
511/04/2018AZTO :Z
618/04/2018AZ19,500RESULT :18500
718/04/2018BZ19,800
826/04/2018AZ
927/04/2018AY
1027/04/2018BY
Skip Empty
 
Upvote 0
Aladin Akyurek said:
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")
Click to expand...

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


Peter_SSs said:
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


ABCDEFG





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
CellFormula
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
Click to expand...

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

Thank you for trying to help me..
 
Upvote 0
You must log in or register to reply here.

Similar threads

sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
551
sharky12345
sharky12345
A
  • Solved
Index/Aggregate Help - Listing All Active Goalies on an NHL Team
Replies
6
Views
127
dreid1011
dreid1011
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
109
kivikatz
K
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
825
juanam
J
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
344
Fuji
F

Forum statistics

Threads
1,224,752
Messages
6,180,743
Members
452,996
Latest member
nelsonsix66

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top