興趣嗜好

收藏本版
發帖
返回列表
跳至頁

首頁
1

尾頁
   0
candyfrog


複式洋房

積分: 309


1#
發表於 14-11-16 01:31 |只看該作者

Hello, I would like to use VLOOKUP to find out all the results of the Lookup value.


For example, I want to find the all amounts of A, it can only show the first row result.




In fact, I want to show out the results of A in order as shown in the following picture.

Anyone can help me to make this formula? Thank you very much.
私人傳訊

lemoncake


複式洋房

積分: 298


2#
發表於 14-11-16 09:33 |只看該作者

回覆：candyfrog 的帖子

You may use SUMIF function
私人傳訊

Aardvark


男爵府

積分: 7559


3#
發表於 14-11-16 14:30 |只看該作者
本帖最後由 Aardvark 於 14-11-16 15:56 編輯
candyfrog 發表於 14-11-16 01:31

... I want to show out the results of A in order as shown in the following picture.

Anyone can help me to make this formula?



Vlookup only return a specific value. List of result need "Index" as an array formula

Now, just copy and paste this formula (and delete the space after the : as ": $" without the space will return icon !!!!)
=iferror(INDEX($B$2: $B$9, SMALL(IF($D$10=$A$2: $A$9, ROW($A$2: $A$9)-ROW($A$2)+1), ROW(1:1))),"")
As a reminder, this is an array formula - rather than hit <Enter>, hit <CTRL> + <SHIFT> + <ENTER>
Now, copy this cell to E11, E12, E13 ... It should do the job
&quot;And liftoff, the final liftoff of Atlantis, shoulders of the space shuttle, America will continue the dream.&quot;

&quot;Liftoff for the final launch of Endeavor.  'xpending our knowledge, 'xpending our lives in space.&quot;

&quot;The final liftoff of Discovery, a tribute to the dedication, hard work and pride of America's space shuttle team.&quot;
私人傳訊

candyfrog


複式洋房

積分: 309


4#
發表於 14-11-17 22:01 |只看該作者
回覆 Aardvark 的帖子

I hv tried this formula but fail to show out the answer.....

私人傳訊


首頁
1

尾頁

跳至頁
返回列表
回覆
發帖

© 2025 Baby-Kingdom.com Limited.

網站： 聯絡管理員 廣告查詢 私隱政策 使用條款 免責聲明 使用意見