VLookup sorting from bottom to top

[Legacy 185660]

Hey guys!
I tried googling this, but the answers I got were way too confusing for me to try to edit.
I need this VLOOKUP formula to search from bottom to top to find the last value.

=VLOOKUP(C3,'Signout History'!A:C,2,FALSE)

Thanks!

 

[Legacy 185660]

and could you make it so that if it does not find that number it returns a ""?

 

[Domenic]

Perfect!
That works!
Thanks a lot!

You're welcome!

Will this work for all rows of C?

I'm not familiar with Google Docs, so you'll have to try it.

...and could you make it so that if it does not find that number it returns a ""?

The IFERROR function can be used, if available on Google Docs. Otherwise, try...

=IF(COUNTIF('Signout History'!$A$2:$A$100,C3)>0,INDEX('Signout History'!$B$2:$B$100,LARGE(IF('Signout History'!$A$2:$A$100=C3,ROW('Signout History'!$A$2:$A$100)-ROW('Signout History'!$A$2)+1),1)),"")

...confirmed with CONTROL+SHIFT+ENTER.

 

[Legacy 185660]

You're welcome!



I'm not familiar with Google Docs, so you'll have to try it.



The IFERROR function can be used, if available on Google Docs. Otherwise, try...

=IF(COUNTIF('Signout History'!$A$2:$A$100,C3)>0,INDEX('Signout History'!$B$2:$B$100,LARGE(IF('Signout History'!$A$2:$A$100=C3,ROW('Signout History'!$A$2:$A$100)-ROW('Signout History'!$A$2)+1),1)),"")

...confirmed with CONTROL+SHIFT+ENTER.

Learned that the array increases automatically by itself, which is perfect!
And thanks! I was using an IF statement with ISERROR, but IFERROR shortens it a bit

Anyway you can shorten:

=IF(D3="","",IF(TODAY()>DATEVALUE(INDEX('Signout History'!$C$2:$C$116,LARGE(IF('Signout History'!$A$2:$A$116=C3,ROW('Signout History'!$A$2:$A$116)-ROW('Signout History'!$A$2)+1),1)))+31,"Late: " & DATEVALUE(INDEX('Signout History'!$C$2:$C$116,LARGE(IF('Signout History'!$A$2:$A$116=C3,ROW('Signout History'!$A$2:$A$116)-ROW('Signout History'!$A$2)+1),1))),DATEVALUE(INDEX('Signout History'!$C$2:$C$116,LARGE(IF('Signout History'!$A$2:$A$116=C3,ROW('Signout History'!$A$2:$A$116)-ROW('Signout History'!$A$2)+1),1)))))

 

[Domenic]

Enter in one cell part of the formula (DATEVALUE/INDEX) that returns the date itself, and then in another cell test the date to see if it meets the criteria. So, for example, maybe something like this...

E3, confirmed with CONTROL+SHIFT+ENTER...

=IFERROR(DATEVALUE(INDEX('Signout History'!$C$2:$C$116,LARGE(IF('Signout History'!$A$2:$A$116=C3,ROW('Signout History'!$A$2:$A$116)-ROW('Signout History'!$A$2)+1),1))),"")

F3:

=IF(D3="","",IF(E3<>"",IF(TODAY()>E3+31,"Late",""),""))

 

[Legacy 185660]

Enter in one cell part of the formula (DATEVALUE/INDEX) that returns the date itself, and then in another cell test the date to see if it meets the criteria. So, for example, maybe something like this...

E3, confirmed with CONTROL+SHIFT+ENTER...

=IFERROR(DATEVALUE(INDEX('Signout History'!$C$2:$C$116,LARGE(IF('Signout History'!$A$2:$A$116=C3,ROW('Signout History'!$A$2:$A$116)-ROW('Signout History'!$A$2)+1),1))),"")

F3:

=IF(D3="","",IF(E3<>"",IF(TODAY()>E3+31,"Late",""),""))

That is almost what I had in the beginning but I wanted it in 1 cell. I think I got it though.
Thanks for all your help!

 

[Domenic]

Does Google Docs allow Defined Names? If so, an alternative might be to use a defined name for IFERROR/DATEVALUE/INDEX, instead of placing it in a cell. This way the defined name is referenced instead of the cell.