INDEX result with date criteria & skip empty cell

P

phithack

New Member
Joined
May 21, 2018
Messages
6
ABCDEF G
1DATEFROMTOPRICE
201-03-18AZ18,900
310-03-18AZ18,500DATE :
429-03-18BZ18,900FROM :
511-04-18AZTO :
618-04-18AZ19,500RESULT :
718-04-18BZ19,800
826-04-18AZ
927-04-18AY
1027-04-18BY

<tbody>
</tbody>

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
1DATEFROMTOPRICE


21/03/2018AZ18,900


310/03/2018AZ18,500
DATE :12/04/2018
429/03/2018BZ18,900
FROM :A
511/04/2018AZ

TO :Z
618/04/2018AZ19,500
RESULT :18500
718/04/2018BZ19,800


826/04/2018AZ



927/04/2018AY



1027/04/2018BY




<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>
</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

J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
556
juanam
J
M
  • Question
Counting specific dates from multiple columns
Replies
2
Views
130
mattbird
M
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
300
awoohaw
awoohaw
matrix26
  • Solved
VBA Macro
Replies
6
Views
455
Peter_SSs
Peter_SSs
SaraWitch
  • Solved
Count number of cells over 18 but discount dates
Replies
3
Views
143
Fluff
Fluff

Forum statistics

Threads
1,214,528
Messages
6,120,064
Members
448,941
Latest member
AlphaRino

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