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.

Comments

Post a Comment

Popular posts from this blog

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

below is my mysql(MariaDB) server stored procedure code # call `pkrss_get_rssitem_vo`(2, 0, 0, 0, 1, 0, 0, 0); # flag = 1: select items # flag =-1: select items count # flag = 2: select items with image mode # flag =-2: select items with image mode count # flag = 3: get top 1 item with each table # flag = 4: get top n item with each table CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT) BEGIN DECLARE s VARCHAR(512); DECLARE s2 VARCHAR(512); DECLARE t VARCHAR(128); DECLARE selImgMode INT DEFAULT 0; DECLARE selCountMode INT DEFAULT 0; IF flag = -1 THEN SET selCountMode = 1; END IF; IF flag = -2 THEN SET selCountMode = 1; SET selImgMode = 1; END IF; IF flag = 2 THEN SET selImgMode = 1; END IF; SET s = ''; IF selCountMode = 1 THEN SET s = CONCAT(s,'S...
Read more

Global can earn money wordpress blog Advertising Alliance Introduction–1 BidVertiser

Image
1.what is bidvertiser Pay per click advertising - online advertising directly on sites of your choice, internet marketing solution for online advertisers. Webmasters make money online. it can earn money for your wordpress blog. Office Web 2.Screen shots 2.1 my blog screenshots In my blog, http://blog.pkrss.com it will show like this: 3.Register and login After your register,it no need audit,and can easy passed. 4.add websites 4.1 "MANAGE BIDVERTISERS” => “Add new BidVertise”: 4.2 checked “Add new BidVertiser under a new domain” Use this option only if you would like to run the BidVertiser ads on another domain name. Please fill in the information of the website you would like to place the BidVertiser ads on. After filling out the form, click "Finish". 4.3 BidVertiser Title: Unique identifier to help you better manage your BidVertisers, for example, "index page", or "about us" i typed “blog.pkrss.com” 4.4 URL of your website: Only e...
Read more

pkrss发布

6763KB RSS新闻阅读器+Windows语音TTS播放+Google Speech语音TTS播放+桌面透明歌词效果+直接文本朗读带记忆功能. Windows 平台下PC桌面应用软件,如果系统语音不喜欢,可以网上搜索中文语音库自己安装,如果不会配置,请到控制面板中找语音,切换默认语音引擎.支持多国语言. 使用QT开发,MAC 与 UBuntu版本,请到pkrss.sf.net下去找,等有时间再发布MAC及UBuntu平台版本.   pkrss is good productor rss reader.it run on windows 's pc plateform,it used windows tts speech engine and google speech engine,play rss,play text speech,transparent lyric effect,you can used it replace your old rss reader,if your don't like the system default engine,your can downloaded the speech engine from web.   http://pkrss.sourceforge.net/pkrss_qt_win32/pkrss_qt_win32_4_1_3.zip
Read more