Hello,
I am trying to build a lookup formula that finds all rows with the lookup date and pulls it into a list.
The formula I have right now works when it is just text. It does not work when it is pulling dates and gives a #NUM! error.
Formula --> INDEX(Find_range,SMALL(IF(Date_range=Find_Date,ROW($Date_range)),ROW()-2),1)
I appreciate you taking time to look into my problem.
All the best.
Use this array formula, copied across and down:
=IFERROR(INDEX(A:A,SMALL(IF($C$4:$C$14=Find_Date,ROW($C$4:$C$14)),ROWS(G$5:G5))),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Or, if you have Excel 2010 or better (not clear from yourprofile... Is there an Excel 2011???), an ordinary formula:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$4:$C$14)/($C$4:$C$14=Find_Date),ROWS(G$5:G5))),"")
Glenn Kennedy, thank you so much! Your formula works like a charm! Would you mind explaining the Aggregate function and the ($C$4:$C:$14)/($C$4:$C$14) portion of it?
Also, my Excel is 2013/2016. I will update my MS-Off Ver.
Using the Evaluate Formula feature may help illustrate that ROW($C$4:$C$14) produces an array {4,5,...14} and ($C$4:$C$14=Find_Date) produces an array of TRUE/FALSE values
The binary for TRUE is 1 and for FALSE is zero so that a ROW number divided by 1 is that ROW and a ROW divided by zero yields #DIV/0
The second argument of AGGREGATE (6) is set to ignore errors so only the numeric values are considered when looking for the Kth smallest. I hope that makes sense.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks