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:
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:
FINAL:
By use current row variable optimized code is:
Last:
Thanks.
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.
Comments