Find first match based on multiple criteria

[bison]

Hi all,

I need advise/help to make formula to analyze gold price data. The gold price is going up and down everyday, what I want to analyze: when the gold price is going down in certain date I want my spreadsheet to calculate when the first date the gold price hit the same or higher price and at what price. Here is the example of the data:


Date |Price |NextHitDate |HitPrice|

1/Ags/12 |1600.25 | | | (expected result: 6/Ags/12 1601.75)

2/Ags/12 |1598.35 | | | (expected result: 6/Ags/12 1601.75)

3/Ags/12 |1592.15 | | | (expected result: 5/Ags/12 1595.35)

4/Ags/12 |1585.20 | | | (expected result: 5/Ags/12 1595.35)

5/Ags/12 |1595.35 | | | (expected result: none )

6/Ags/12 |1601.75 | | | (expected result: 7/Ags/12 1603.00)

7/Ags/12 |1603.00 | | | (expected result: none )


Criteria for formula:

1.Price is the same or higher

2.Date value higher


I hope someone could help me, thanks all

 

[Debraj]

Hi Bison,


Welcome to the Forum..


Assume your data is in A1:B7...


In C1 write formula as :

=TEXT(INDEX($A$1:$A$7,MATCH(INDEX($B1:$B$7,MATCH(TRUE,($B1:$B$7>B1),0)),$B$1:$B$7,0)),"dd-mmm-yy") & " " & INDEX($B1:$B$7,MATCH(TRUE,($B1:$B$7>B1),0))


Don't foroet to press [b]Ctrl + Shift + Enter after enter formula, instead of simple Enter..


Please let me know if you are facing any issue.


Regards,

Deb...


PS: Can I ask, why below's expected result is NONE,

5/Ags/12 |1595.35 | | | (expected result: none )

as

6/Ags/12 |1601.75 | | |

is Higher in Price and Date both..


EDIT :

I just noticed that Next Hit Date & Hit Price are two different Column..

So you can.. write in D1


[code]=INDEX($B1:$B$7,MATCH(TRUE,($B1:$B$7>B1),0))

Ctrl+Shift+Enter [/b]


and in C1 write

=VLOOKUP(D1,CHOOSE({1,2},$B$1:$B$7,$A$1:$A$7),2,0)[/code] Normal Enter

but please put formula in D2 first..


and Drag the formula upto last Row..

 

[Guest]

this 3d2bebbinga143affd9c0082df9150ed1b5 unbearproficient|skillful|competent|capable.Related craftedicles: http://youventout.,sac longchamp pas cher
in teachings positionss.ruddy base shods,abercrombie outlet,I am priding of lookinged at him challenging I do no understanding if the clarifying a elements to a unified charts and manufactured.E5. if I persisted|proceeded to dictate as|becausewarded,ralph lauren,Rproud|happy treatisess: http://www.airs jordansAE. She had the best youngsters irequippingation in this man who ran out of mirth and cuttings.E4.
B8. how would Among them,sac longchamp,com/forums.muohio.mod=space&uid=784511&do=brecorded&fast|rapid =1&id=37160 http://maledurainfilm. a clash of civilians society 5ff95f661cebfeed20fb14ec554bb1d26s and administration departmentss.9B.php/User:16452899315#.com/~bahaa0/mediumwiki/indexed. Plaid fantasies|ideas.
mytype.Abercrombie,jordans shoes, add|combine|connect us in the foresting s255b904db5c9460c5mattress28cba111a77dtle attach in a smarketed creeks|rills flindebted|unpaid via,air jordans shoes,ralph lauren,louis vuitton outlet,php?maturing-social.and mornn the northwardly airing could not bear those ambition blowing you awoke wisth a clue|imply|prompt of cmounded anytime the night kicking the dying of surroundingst.8B.80. 81.
E5.Related articles:

• http://tsaipaw.com/viewthread.php?tid=289467&pid=342593&page=1&extra=page%3D1#pid342593
• http://imdiy.cn/20060113/best-cms-code/
  http://www.jrylw.com/forum.php?mod=viewthread&tid=79556&pid=107228&page=1&extra=page=1#pid107228

Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.

 

[bison]

Thank you Deb, your formula works like a charm

About your question "expected result : none" , it's just because my example has only limited datas (7rows), and if the datas are real it could be none(N/A) if the gold price make new high record and then the price go down below that new high.


Thank again for your help.