Reverse Excel

[zazybang]

Hey guys, when using the INDEX(MATCH function it will find the very first row that meets the criteria. How would i make it find the last row that has said value?

 

[Tom Urtis]

Can you elaborate.
You wrote:
"How would i make it find the last row that has said value"
You only want to know the last row number?
You want the value in some cell in the last row of the criteria?
Does the criteria exist several places in a column (if so, which column) and you want to find a value in another column (if so, which column) on that same row?
Or maybe your worksheet is set up totally different or your question is about something totally different. If so, need more info.

 

[zazybang]

Ok, so i linked a response form to my spreadsheet. On sheet A i have a list of name in column A. On the response sheet, i have column A giving dates based on user input. On sheet A column B, I need a formula that will place the date of the lastest response from the name the date corresponds with EX:
Bobby fills out a form March 8th, Bobby once again fills a form but this time its on March 9th. I need the formula to list the date of his LATEST response.

 

[Aladin Akyurek]

Which column houses the names?
Which column houses the dates?

 

[zazybang]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Names[/TD]
[TD="align: center"]Dates[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
[TR]
[TD="align: center"]x[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
[TR]
[TD="align: center"]f[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
</tbody>[/TABLE]

Response Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]May 13th[/TD]
[/TR]
</tbody>[/TABLE]
^I want the formula to take this data as its more recent than row 1

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,1/(NamesRange="zazybang"),DatesRange)

This returns last date for zazybang. If dates in DateRange is in chronological order, the last date = the most recent date.

 

[mandukes]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Names[/TD]
[TD="align: center"]Dates[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
[TR]
[TD="align: center"]x[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
[TR]
[TD="align: center"]f[/TD]
[TD="align: center"]=Input formula[/TD]
[/TR]
</tbody>[/TABLE]

Response Sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Names[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]May 12th[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]May 13th[/TD]
[/TR]
</tbody>[/TABLE]
^I want the formula to take this data as its more recent than row 1

It isn't simpler than it looks, You cannot simply return the most recent date for each name simply by the formula. I guess you need a vba function to do the same.

although, I tried to formulate it with formula but could only return name having the most recent date

here is a code

=IF(INDEX($B$5:$C$7,MATCH(E5,$B$5:$B$7,),2)=MAX($C$5:$C$7),(INDEX($B$5:$C$7,MATCH(E5,$B$5:$B$7,),2)))

 

[zazybang]

It isn't simpler than it looks, You cannot simply return the most recent date for each name simply by the formula. I guess you need a vba function to do the same.

although, I tried to formulate it with formula but could only return name having the most recent date

here is a code

=IF(INDEX($B$5:$C$7,MATCH(E5,$B$5:$B$7,),2)=MAX($C$5:$C$7),(INDEX($B$5:$C$7,MATCH(E5,$B$5:$B$7,),2)))

i ended up having to use a query formula in order to have responses placed in ascending order rather than descending

 

[Aladin Akyurek]

Did you try the #6 suggestion at all?

 

[zazybang]

Not yet I will try it on a test spreadsheet, Your formula is too godly for me to understand lol