Question

关于excel表格函数的问题？

=IFERROR(LOOKUP(1,0/({"水稻","玉米","小麦","大麦","干蚕豆","干豌豆","大豆","杂豆","油菜籽","花生米","葵花籽","甘蔗","白菜","苦菜","萝卜","青椒","莲花白","茄子","西红柿","青蚕豆","青豌豆","荷兰豆","四季豆","马铃薯","山药","莲藕","烤烟","魔芋","苹果","桃子","梨","樱桃","芒果","香蕉","石榴","橘子","核桃","板栗","花椒"}=A9),{485,415,235,245,165,135,165,185,140,210,110,3650,2850,2150,3650,1100,2850,1300,3850,350,650,550,650,1650,900,2150,135,1650,2150,3150,3150,450,3150,3150,2100,2100,250,550,225})*C9,0)，这个公式里面我想把倒数第13个135换成一个vlooup函数，要怎么实现呢

Answer 1

整个公式用VLOOKUP设置公式：
=IFERROR(VLOOKUP(A9,{"水稻",485;"玉米",415;"小麦",235;"大麦",245;"干蚕豆",165;"干豌豆",135;"大豆",165;"杂豆",185;"油菜籽",140;"花生米",210;"葵花籽",110;"甘蔗",3650;"白菜",2850;"苦菜",2150;"萝卜",3650;"青椒",1100;"莲花白",2850;"茄子",1300;"西红柿",3850;"青蚕豆",350;"青豌豆",650;"荷兰豆",550;"四季豆",650;"马铃薯",1650;"山药",900;"莲藕",2150;"烤烟",135;"魔芋",1650;"苹果",2150;"桃子",3150;"梨",3150;"樱桃",450;"芒果",3150;"香蕉",3150;"石榴",2100;"橘子",2100;"核桃",250;"板栗",550;"花椒",225},2,)*C9,0)
如果从倒数13个（135）开始，公式：
=IFERROR(VLOOKUP(A9,{"烤烟",135;"魔芋",1650;"苹果",2150;"桃子",3150;"梨",3150;"樱桃",450;"芒果",3150;"香蕉",3150;"石榴",2100;"橘子",2100;"核桃",250;"板栗",550;"花椒",225},2,)*C9,0)