Thursday, April 5, 2018

How To change Vlookup Matching Value From Bottom To Top In Excel 2016?

What?
How To Vlookup Matching Value From Bottom To Top In Excel?

Why?
Because default excel vlookup function search direction is from top to down, i need from down to top in finance data analyse.

my want formula is:
=VLOOKUP(G28,G27:H2,2,FALSE) but excel change it to =VLOOKUP(G28,G2:H27,2,FALSE)

How:
I Find answer from here: https://www.extendoffice.com/documents/excel/3404-excel-vlookup-from-bottom.html

Last i get the right formula is:
=LOOKUP(2,1/($G$2:$G$27=G28),$H$2:$H$27)


FINAL:

By use current row variable optimized code is:
=LOOKUP(2,1/(INDIRECT(CONCAT("G2:G",VALUE(ROW()-1)))=INDIRECT(CONCAT("G",ROW()))),INDIRECT(CONCAT("H2:H",ROW()-1)))


Last:
Thanks.

No comments: