Hi there,
I'm searching for a formula which will return me the*row number*for the row containing the highest date (column A) together with a given lookup value (column B). So the outcome would be '16.02.2015', in the attached xls.
Would like this not to be a matrix formula, as matrix formulae tend to slow down my spreadhseet formula when copied through over many rows.
Greatly appreciated if someone could help me.
Thanks,
Niels
Last edited by nielsb; 07-21-2015 at 06:10 PM.
Does this work for you?
Please Login or Register to view this content.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
no, doesn't work..
Was it the relative references that didn't work when you drug it down the column? This will fix that:
Was there something else that didn't work. It appears to work in the attached file.Please Login or Register to view this content.
... worked for me to simply entered into E6.
I don't understand... The formula works in the xls you uploaded.. But when I copy it into my own spreadsheet it doesn't...
I attached a stripped down (for filesize reasons) copy of my own spreadsheet. Can anybody have a look why the formula isn't working here?
The "N/A' in your date column messes up the formula. Remove that and then try it
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
The "na" value in line 12 is messing up the formula. Is it possible to simply delete that row?
@NielsB
That is much to easy, please tell the forummembers why it is not working for you.Please Login or Register to view this content.
See #4 ad #5 they show it is working.
My solution with a pivot table.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
This was the trick... The NA indeed messed up the result.. Only now I see to have given wrong instruction in the sheet.. I need to have the row number returned not the date.. What would the formula look like??Originally Posted by ChemistB
Probably with index / match.Please Login or Register to view this content.
What are you gonna do with that information?
Please explain more of your goal.
The row number will be used to form adresses which will be used to provide information (some are text) in several adjacent columns. At first I had matrix formulae in all these columns, but the sheet got terribly slow that way..
Last edited by nielsb; 07-21-2015 at 05:16 PM.
This is not clear to me, maybe for other forummembers.Please Login or Register to view this content.
See the attached file, for the rownumbers in the pivot table.
If this give the correct answers, an index / match formula can show the result (dutch => index / vergelijken).
This should give you the row number.
Please Login or Register to view this content.
@oeldere, I appreciate your effort.. But the pivot table doesn't help me..
I have restated the question in attached sheet..
Please also, reply to the other forummember who have given an answer to you.
Maybe the answer is already there.
@nigelbloomy, Can you upload an xls with the formula working?
@nigelbloomy,
can you upload an xls with the working formula?
I believe this formula in B2 works on the FINAL tab.
Please Login or Register to view this content.
excuse me, I used the sum in the pivot table.
of course it had to be the max (value).
see the attached file.
in nigelbloomy's formula change the row into rij
Please Login or Register to view this content.
Last edited by oeldere; 07-21-2015 at 05:49 PM.
Nigel, your post helped me perfectly.. Thanks a great deal!
You're welcome. We appreciate the feedback!
It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
Thank you. It was a team effort.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks