<menu id="suuyg"><code id="suuyg"></code></menu>
  • <menu id="suuyg"><nav id="suuyg"></nav></menu>
    <tt id="suuyg"><tt id="suuyg"></tt></tt>
  • <xmp id="suuyg">
  • <nav id="suuyg"></nav>
    <nav id="suuyg"></nav>
    Question to say "I can!"

    Excel最全逆向查詢(VLOOKUP,INDEX,LOOKUP)

    2016-12-16

    在日常的Excel函數應用中,最常用的就是查詢類函數,比如說根據工號查詢姓名、根據學生查詢成績、根據男豬腳查詢女一號等等。

    說到查詢類函數,大家對VLOOKUP一定不會陌生,這可是人見人愛花見花開的函數。

    如下圖,查詢明星的配偶:

    E2單元格公式為
    =VLOOKUP(D2,A:B,2,0)
    意思就是以D2單元格的姓名作為查詢值,以A:B為查找區域,在首列中找到與D2單元格相同的姓名,然后返回這個區域中與之對應的第二列(也就是配偶所在列)的配偶。

    如果我們以配偶作為查找值,需要在這個區域中查找和配偶對應的姓名,該如何使用公式呢?

    因為VLOOKUP函數要求查詢值必須處于查詢區域的首列,再使用普通方法就無法完成要求了,今天就和大家說說,關于逆向查詢的幾種方法。

    方法一:使用IF函數重新構建數組。

    在E4中輸入
    =VLOOKUP(D4,IF({1,0},$B$2:$B$17,$A$2:$A$17),2,0)

    這個公式的用法就是用IF({1,0},$B$2:$B$17,$A$2:$A$17),返回一個配偶在前,姓名在后的多行兩列的內存數組,使其符合VLOOKUP函數的查詢值處于查詢區域首列的條件,再用VLOOKUP查詢即可。

    該函數使用比較復雜,運算效率低。

    裝逼指數:★★★★★
    推薦指數:★

    方法二:使用CHOOSE函數重新構建數組。

    E6使用公式為:
    =VLOOKUP(D6,CHOOSE({1,2},B2:B17,A2:A17),2,0)

    這個公式的原理也是重新構建一個內存數組,使其符合VLOOKUP函數的查詢值處于查詢區域首列的條件。

    該函數與方法一的思路相同,同樣是使用復雜,運算效率低。

    裝逼指數:★★★★★
    推薦指數:★

    方法三:INDEX+MATCH結合使用。

    E8使用公式為:
    =INDEX(A:A,MATCH(D8,B:B,))

    公式首先使用MATCH函數返回D8單元格姓名在B列單元格中的相對位置,也就是這個區域中所處第幾行。

    再以此作為INDEX函數的索引值,從A列單元格區域中返回對應位置的內容。

    這個公式是最常用的查詢公式之一,看似繁瑣,實際查詢應用時,由于其組合靈活,可以完成從左至右、從右到左、從下到上、從上到下等多個方向的查詢。

    該函數是嵌套使用,操作靈活,運算方便。

    裝逼指數:★★★
    推薦指數:★★★

    方法四:所向披靡的LOOKUP函數。

    E10使用公式為:
    =LOOKUP(1,0/(D10=B2:B17),A2:A17)

    這是非常經典的LOOKUP用法。

    首先用D10=B2:B17得到一組邏輯值,再用0除以這些邏輯值,得到由0和錯誤值組成的內存數組。再用1作為查詢值,在內存數組中進行查詢。

    如果 LOOKUP 函數找不到查詢值,則它與查詢區域中小于或等于查詢值的最大值匹配,因此是以最后一個0進行匹配,并返回A2:A17中相同位置的值。

    該函數使用簡便,功能強大,公式書寫也比較簡潔。

    裝逼指數:★
    推薦指數:★★★★★

    如果有多條符合條件的結果,前三個公式都是返回首個滿足條件的值,而第四個公式則是返回最后一個滿足條件的值,這一點大家在使用時還需要特別注意。

    關于多條件查詢,可以《一對多查詢,神器index+small》。

    作者:admin | Categories:辦公能手 | Tags:

    發表評論

    電子郵件地址不會被公開。 必填項已用*標注

    *

    日彩网平台日彩网主页日彩网网站日彩网官网日彩网娱乐 www.09888cp.com | www.3589333.com | 3552d.com | www.4996jy.com | 019666.com | www.21202m.com | www.774426.com | www.109883.com | www.110483.com | hgw168v.com | www.55676y.com | 2381qq.com | www.847980.com | www.848464.com | 4066r.com | www.r94600.com | 996622qq.com | www.151220.com | www.874858.com | 01860.net | www.8039r.com | www.4270gg.com | 00048o.com | www.y98c.com | www.76060r.com | 18894.com | www.vip9580.com | 3fll8.com | www.ascp3.com | www.5601j.com | 2267z.com | www.55717w.com | www.767166.com | 2934k.com | www.005469.com | 33318t.com | www.526031.com | www.v4042.com | afcp10.com | www.hm6633.com | www.z6777.com | 4007893.com | www.czg1.com | www.5f33.com | 998k.cc | www.569080.com | www.668cp33.com | 4379o.com | www.743552.com | www.751cp22.com | 9339999f.com | www.234279.com | www.b35pp.com | a3144.com | 0234oo.com | www.883399c.com | www.6491c.com | yabo9000.com | www.71399z.com | www.xh4477.com | 7xh.vip | www.55cmc.com | www.yingle33.com | 6766gg.com | www.80075o.com | www.w84d.com | www.87680g.com | tz1333.com | www.hcjt8.com | www.16602g.com | hjdc789.com | www.346277.com | www.9570115.com | www.xinhao03.com | 33cc8332.com | www.645977.com | www.9951331.com | 8766545.com | mgm3242t.com | www.5484h.com | www.f9478.com | 7720h.com | 7742r.com | www.495414.com | www.588778.com | 0507.com | www.603854.com | www.j3567.com | www.85770h.com | 45637i.com | www.689144.com | www.115527o.com | www.hg9922.org | 77606v.com | www.87668v.com | www.656659.com | www.582666.com | 4541p.com | 00nn8331.com | www.43818c.com | www.kj639.com | 9995.cc | 1479x.com | www.810629.com | www.c063801.com | www.7111t.com | 610829.com | 8464999.com | www.33997q.com | www.5504j.com | 3644x.com | 0343f.com | www.160882.com | www.4331a.com | www.2005132.com | 3136mm.com | 33678hh.com | www.629813.com | www.7240r.com | www.43380a.com | n47479.com | 93922q.com | www.369053.com | www.79566.com | www.807239.com | www.653505.com | js8068.com | 889345.com | www.840680.com | www.cb600.cc | www.68277666.com | www.56520q.com | 84496688.com | 38365z.com | www.163910.com | www.50026m.com | www.4996jm.com | www.3170004.com | m0083.com | 1443b.com | 55331t.com | www.hx6666.com | www.806765.com | www.x4042.com | www.j833.com | www.64400a.com | w7667.com | 30171133.com | 175030.com | www.837771.com | www.983258.com | www.16181w.com | www.hg0180.com | 3659.com | d62365.com | 54141133.com | 3024r.com | www.424223.com | 365225d.com | 4461z.com | ss2649.com | 20160913.tv | www.806726.com | www.08588v.com | www.3157s.com | www.hg7861.com | www.7720r.com | pc5162.cn | 717blr.com | 205.com | 64111g.com | www.570831.com | www.055g.cc | www.68993262.com | www.491977.com | www.5966qqq.com | www.16297755.com | www.349377.com | www.hm3377.com | www.66653m.com | www.xpj716.com | www.848777g.com | www.322113.com | www.32666r.com | www.0636e.com | feicai0411.com | r45638.com | nn1915.com | drcp222.com | 80850n.com | xsj.com | 77552007.com | www.062866.com | www.610330.com | www.x34.com | www.39500j.com | www.t639.com | www.37377o.com | www.8905w.com | www.j3410.com | www.234kk.com | www.twcp04.com | www.487575a.com | www.09528.cc | www.6372010.com | www.699888.com | www.5511sb.com | www.63877t.com | www.9544j.com | www.6667727.com | 5077777.com | 3121ss.com | 2455i.com | 8036cc.com | 44yy8331.com | www.0686333.com | 66458r.com | 3659h.cc | www.5487567.com | www.laok000.com | www.a8a8789.com | www.jsw66.cc | www.414133.com | www.xpj8878.com | www.xpj9878.com | www.ydb44.com | www.5678655.com | www.8181msc.com | www.sun7708.com | www.7111k.com | www.7111w.com | www.2077w.com | www.9k365.com | www.k555555.com | www.hg7949.com | www.o4042.com | www.tc8803.com | www.4212z.com | www.hg8oo.com | www.hgdc100.com | www.0055sun.com | www.495.cc | www.954321n.com | www.60123x.com | www.090.la | www.911050.com | www.567306.com | www.50026u.com | 444365ggg.com | sbd0022.net | 658pj.com | 60166yh.com | 4556h.com | 00ww8332.com | www.44466638.com | www.qiudala.org | www.bet3650914.com | www.cp0016.com | www.158024.com | www.4988b.cc | www.pkfc8.com | www.599849.com | www.hcw018.com | sha145.com | 4116i.com | xpj66112.com | 8577m.cc | www.pj5938.com | www.1234118.com | www.11vn777.com | www.k63568.com | www.50024x.com | www.802190.com | www.50051v.com | 696.net | 060nnn.com | 8957a.com | www.xsj900.com | www.58js88.com | www.677256.com | www.32031l.com | www.71233k.com | www.769639.com | 666ths.com | 55797y.com | 9101902.bet | www.4759oo.com | www.40686b.com | www.xpj2010.com | www.9994331.com | www.hm2666.com | xpj01988.com | 7605s.com | 56988j.vip | www.825909.com | www.97596.com | www.pj9010.com | www.ybao9.com | bet8688.com | agaa29.com | 2142288.com | www.56520n.com | www.66166i.com | www.80969z.com | www.4923l.com | www.639477.com | ff555i.com | 86811qq.com | www.9374s.com | www.hp5868.com | www.4809p.com | www.988068.com | 4195z.com | s3405.com | www.xgmhh.com | www.56655w.com | www.980442.com | www.569380.com | aobo665.com | 2844u.com | www.sun368.com | www.14168f.com | www.960207.com | 0860l.com | 11vv8331.com | www.w8586.com | www.4521v.com | www.u936.com | 58222s.com | 6911555.com | www.xpj66989.com | www.9422a.com | www.268177.com | qy002.vip | www.8134.cc | www.50054.com | www.34207.com | long2268.com | 3245q.com | www.0977711.com | www.0343w.com | www.550263.com | 55545q.com | www.yh77701.com | www.98698k.com | www.1248g.cc | 3473o.com | 6641ss.com | www.5504s.com | www.73166.com | 5622o.com | c63.cc | www.998762.com | www.43818f.com | 2934d.com | 83377f.com | www.55526n.com | www.9928l.com | e1327.com | bb888899.com | www.8473f.com | www.858899.com | 3559yy.com | www.pj9909.com | www.xpj5.net | www.0377555.com | 6701n.com | www.886242.com | www.68689n.com | 12742v.com | 44335003.com | www.659202.com | www.500021.com | 3640u.com | www.163888ll.com | www.83993z.com | 3078v.com | 47300.com | www.9570119.com | www.444219.com | 4340o.com | www.4965a.com | www.8667o.com | 796666.com | www.87680n.com | www.bjl916.com | 2709q.com | 5001f.com | www.590561.com | www.108097.com | 36566.bet | www.8967g.com | www.669773.com | 4022vv.com | www.899033.com | www.915319.com | 3748.com | www.01593.com | www.661526.com | 365spa.com | www.sj52188.com | www.610390.com | 3522s.vip | www.bd2019e.com | www.444246.com | pjjiangsu.com | www.55060q.com | www.187132.com | 2373n.com | www.4196w.com | dzj0666.com | www.405776.com | www.vn888567.cc | n7454.com | www.amxhtd11.com | www.082wy.com | hc333.vip | www.jy883.com | www.529061.com | 3156vvv.com | www.y8862.com | 4195d.com | www.alpk888.com | www.76520q.com | 6572t.com | www.1181184.com | www.355204.com | 3136ii.com | www.1434e.com | 1227010.com | xxx8827.com | www.76543f.com | www.36788y.com | 3559t.com | www.1064b.com | 0241h.com | www.79884.com | www.15355g.com | 13222i.com | www.89599e.com | hg77712.com | www.0151u.com | www.535666a.com | 98345h.com | www.087w.com | 7945ll.com | www.2y933.com | 496.be | www.40818b.com | www.371n.cc | 4809v.com | www.522665.com | t365j.com | www.hjcp77.com | 7720776.com | www.q22365.com | www.c5905.com | 3225n.com | www.0601p.com | lll67890.com | www.377666n.com | y6840.com | www.393222.com | www.cc444.com | 66300vip45.com | www.8188zr.com | www.702152.com | 1495.com | www.5xcj.com | hkmh44.com | www.77802h.com | pj9937.com | www.x98478.com | 4590bb.com | www.9895e.com | 871ww.com | www.7415b.com | 58222cc.com | www.35252p.com | 91019k.com | www.sj016.com | 3434zz.com | www.987788.com | bet2018365.com | www.6666zs.com | www.103380.com | www.330099u.com | www.308680.com | www.4972pp.com | www.399047.com | www.f30226.com | www.370837.com | www.655666i.com | www.335324.com | www.pj2018.com | www.202423.com | www.78.tt | www.35918e.com | www.cp08e.com | b762.xyz | www.bct5123.com | yh888l.com | www.565560.com | 5350.com | www.ldz333.com | 08159k.com | www.hr1844.com | vip7033.com | www.66598b.com | 30006g.com | www.ac3311.com | www.32126t.net | www.384918.com | www.742622.vip | jj5443.com | www.x948x.com | 3246.com | www.3846e.com | 2096x.com | www.35155w.com | 54443h.com | www.533508.com | www.ubo3388.com | 667766z.com | www.5446cc.com | 444000ff.com | www.7714g.com | 9539i.com | www.594877.com | www.838967.com | 3169c.com | www.js8888888830.com | 3308.com | www.9155b.cc | www.b0999.com | 8203ccc.com | www.ly19.com | 4340v.com | www.68682g.com | www.ylhg4848.com | www.50024e.com | www.7agcpw.com | 99909i.com | www.15355v.com | www.aobo22.com | www.61233i.com | 40014499.com | www.ihg5533.com | 7196bb.com | www.317990.com | www.140suncity.com | yl6944.com | www.77801j.com | www.70060000.com | 7737.com | www.62979c.com | 3254.com | www.345512.com | www.boao555.com | 47470524.com | www.00711.cc | www.4449193.com | 5443mm.com | www.668679.com | 4048vip3.com | 3202q.com | www.9895q.com | 8036y.com | www.369062.com | www.85857s.com | 36408899.com | www.911790.com | www.ra7088.com | g8009.com | www.959zf.com | www.8837138.com | 5201314777.com | www.23579f.com | www.hg2425.com | ylg2001.com | www.52303y.com | www.558077.com | 32355.com | www.87668t.com | www.xin888999.com | 1294i.com | www.15355v.com | www.sun7707.com | 30688.com | www.00665h.com | www.a3a444.cc | 11zz145.com | www.gczj6.com | www.597567a.com | ee5002.com | www.506951.com | www.8888hj.com | 6220yy.com | feicai0953.com | www.111999qipai.com | www.223456r.com | 131ww.net | www.31847.com | www.872878.com | 3308.com | www.295570.com | www.js79902.com | www.1869g.com | 0860b.com | www.91233r.com | www.03688.com | 3522hh.cc | www.587989.com | www.mgm868004.com | 3333xsj.com | 673bet.com | www.50074j.com | www.67007.com | 6766.com | 00074066.com | www.vn888567.cc | www.975426.com | 36796.com | www.303053.com | www.wns123g.com | www.bet365789.com | jixiangfang.com | www.926013.com | www.1100xpj.net | www.wst.vip | 2998v.com | www.902776.com | www.bwinyz18.com | www.1818xl.com | pjxinjiang.com | www.756398.com | www.678js555.com | www.yy883.com | hg0068s.com | www.301345.com | www.350c6.com | www.vn22dd.com | wnsbc.com | 2934j.com | www.35918d.com | www.710jcjc.com | 30006g.com | 3983009.com | www.847377.com | www.33678xx.com | www.77cmsc.net | 36404400.com | jmszszx.com | www.36787j.com | www.919205.com | www.fh7777.com | 98345o.com | www.030310.com | www.546230.com | www.bm246.com | 6363d.com | 655661144.com | www.349177.com | www.ihg5533.com | www.2206132.com | 47749l.com | 30170022.com | www.50024e.com | www.77996c.com | www.138136.com | www.pjdc2288.com | 3807.com | 65005c.com | www.am7588.com | www.12136z.com | www.xpj195.com | 5002rrr.com | 2021eee.com | 2012vip77.com | www.1248h.cc | www.js9595.com | www.62007.com | 01dd.cc | 80892a.com | 88447.com | www.209576.com | www.js9615.com | www.892558.com | 亿客隆.com | 8003822.com | www.386918.com | www.79520c.com | www.9hg6668.com | www.66300e.com | 5002s.com | 38365m.com | 7894q.com | www.370837.com | www.50080z.com | www.y063801.com | www.678987.com | www.323788.com | 00xx8331.com | 30006.cc | 20772288.com | www.498266.com | www.35918k.com | www.258908.com | www.g4042.com | www.cp5000e.com | 2306l.com | 4018ss.com | 33889737.com | 196600.com | www.621351.com | www.33588w.com | www.78680m.com | www.yzcp2026.com | www.09323.cc | www.28758v.com | 500000419.com | ww3336.com | 1211.cc | 500000897.com | www.493351.com | www.1035a.com | www.2418000.com | www.623906.com | www.2y925.com | www.866666c.com | www.38200c.com | 33115u.com | 4488v.com | c96sg2.com | 463q8.com | 3178d.com | www.202423.com | www.847577.com | www.9478w.com | www.0194009.com | www.ba506.com | www.04567g.com | www.r8877.com | www.78k78.com | www.028082.com | www.620316.com | www.rrle2.com | www.34788h.com | www.8887989.com | www.42070017.com | www.58404a.com | www.38345n.com | www.38238y.com | www.tqdc222.com | www.0099ooo.com | www.bb5577.com | www.yqf2.com | ss888ss888.com | mgm3242.com | 7720x.net | a4647.com | 3656jj.com | 8037dd.com | 58222n.com | 5201314777.com | 30019v.com | 77889193.com | 8118522.com | 11005h.com | zgfcw.cc | 55984d.com | 3405ggg.com | 7240e.com | jsjlb33.com | 76668.com | 2267j.com | 88304k.com | 66300vip14.com | 20188v.com | 91019n.com | 9149u.com | 9420a.com | x48e.com | k9539.com | 2381b.com | 1407.cc | 134767.com | htbiochem.com | 30175544.com | dzj224.com | 22098m.com | sz8222.com | 4486699.com | 33318q.com | 2061155.com | 3245d.com | 9949x.com | x48o.com | www.7720v.com | www.56520b.com | www.25288b.com | www.8577l.cc | www.vns1033.com | www.68277999.com | www.00618f.com | www.06820t.com | www.wns55.me | www.89777n.com | www.07163v.com | www.53168.com | www.flcb5.com | www.699925.com | www.288472.com | hd61788.com | afcp.com | 3834503.com | 2021c.com | 55797h.com | 56988.vip | www.pj7333.com | www.ty971.com | www.7736.com | www.lyjcp.cc | 3559b.com | 655660000.com | 1429i.com | www.1347-05.com | www.hg94777.com | www.102149.com | www.00772p.com | www.86339s.com | www.86267y.com | www.hy2733.com | www.321953.com | 6363i.com | hg12020.com | 3640jj.com | xpj888.com | www.hg8728.net | www.889966.com | www.89599f.com | www.pj9030.com | www.16878t.com | www.608587.com | 99044.com | 7726nnn.com | aa5002.com | y00351.com | www.msc99.la | www.2945f.com | www.79095s.com | www.110073.com | www.2109d.com | www.257630.com | 56988h.vip | 3890l.com | 50128e.com | www.hg5588k.com | www.1www.0079988.com | www.4196w.com | www.22062d.com | www.755017.com | 2776x.com | 11228016.com | 55899x.com | www.r766.com | www.rgcp.com | www.hg192.com | www.cp718.com | 55ff8332.com | 5020.com | 5589y.com | www.8808000.com | www.ly19.com | www.386123.com | www.901512.com | 66339193.com | 7555i.com | www.854556.com | www.322833.com | www.074546.com | www.2373m.com | 9958944.com | 029.com | 51200jj.com | www.886889.com | www.ba306.com | www.33fzc.com | 228888c.com | 2997771.com | www.8520v.com | www.hk8688.com | www.9971002.com | www.783802.com | 55909.com | 310577.com | www.777cm1.com | www.38138a.com | www.5555y.cc | yhw7.cc | 4022v.com | www.yh1555.com | www.50999e.com | www.60007k.com | w69441144.com | 00755b.com | www.9374o.com | www.98698w.com | www.50024f.com | 2096g.com | 28365.com | www.995n.net | www.4058ll.com | www.zuan444.com | 8577k.cc | 2905.cc | www.652005.com | www.5zz66.com | www.399047.com | 4647i.com | www.hd585.com | www.sscb33.cc | www.05500w.com | 8970399.com | 6320666.com | www.585555.com | www.5657598.com | www.137103.com | 55967f.com | www.40686f.com | www.88325t.com | www.775020.com | 8668yh.com | www.4123020.com | www.dzj0555.com | www.ban54.com | z7454.com | d55.com | www.55545y.com | www.w695.com | 00qq8332.com | 5010.com | www.04567t.com | www.am7654.com | 55967e.com | www.8494i.com | www.91779e.com | www.574177.com | 77112007.com | www.0860i.com | www.57777.am | 20188s.com | 28839l.com | www.www-58944.com | www.15355y.com | vv1331.com | zz38648.cc | www.00778y.com | www.609719.com | 3807g.com | www.299755k.com | www.25688i.com | 2329.com | 1168m.com | www.40598.com | www.325826.com | 8883805.com | www.bmw9968.com | www.2934f.com | 3559uuuu.com | www.l22365.com | www.68993257.com | 6641qq.com | 507805.com | www.4196f.com | www.179237.com | 61326655.com | www.5504e.com | www.860836.com | y7064.com | www.001547.com | www.2632h.com | hg999333l.com | www.1166hg.com | www.77801p.com | 1434c.com | www.xin888999.com | www.4923p.com | 5446f.com | www.h7444.com | www.6889796.com | 50067o.com | www.32126m.net | www.567111.net | 3121h.com | www.ab142.com | www.ylylc09.com | 78119900.com | www.197093.com | 2306w.com | www.35700d.com | 30019a.com | 21866d.com | www.12136a.com | 23225.com | www.blr0055.com | www.1466q.com | bet99969.com | www.88837u.com | www.33588w.com | 095.com | www.msc555.com | www.755096.com | 8522llll.com | www.bwinyz42.com | 32424c.com | www.1111jsc.com | www.410087.com | 06382525.com | www.8888888b.com | www.076wy.com | 9993865.com | www.271998.com | www.52072l.com | 3616v.com | www.770802.com | 9411ccc.com | www.cr3456.com | www.21202ii.com | 7570.com | www.5719f.com | 15a43.net | www.330099h.com | www.5578w.com | 3640bb.com | www.00772p.com | cr789.co | www.33928a07.com | www.51331k.com | 3242o.com | www.hga55500.com | 78111155.com | www.yh8333.com | www.00840y.com | 3467q.com | www.jz3666.com | 2224661.com | www.hg5588.me | www.2875c.com | swin10.com | www.56730.com | 11998332.com | www.xj555666.com | www.857380.com | 3413222.com | www.698505.com | 59889d.com | www.01.bet | fff2267.com | www.7830n.com | www.857t.com | 61324477.com | www.33074.com | j77304.com | www.tycyl.cc | 7742dd.com | www.701439.com | www.472717.com | www.a3a555.cc | www.25688g.com | qifa168.net | www.44074.com | 98345p.com | www.hs6666.com | 2776j.com | www.flp1681.com | 55ww8332.com | www.hg2788.com | www.324246.com | www.39695x.com |