Excel - 从一列中水平返回多个匹配值 [英] Excel - Return multiple matching values from a column, horizontally in one row
问题描述
我有这个表:
AB
1 | | |
2 |橘子| 1 |
3 |苹果| 2 |
4 |葡萄| 3 |
5 |橘子| 4 |
6 |苹果| 5 |
7 |葡萄| 6 |
8 |苹果| 7 |
我想检查A列中的匹配值,如苹果,橘子等。并从列B中返回一行中的所有相应值:
输出应该是这样,但我只得到#VALUE:
ABCD
11 |苹果| 2 | 5 | 7 |
12 |橘子| 1 | 4 | #NUM |
这是公式:
= INDEX($ B $ 2:$ B $ 8,SMALL(IF($ A $ 11 = $ A $ 2:$ A $ 8,ROW($ A $ 2:$ A $ 8)-ROW($ A $ 2) +1),COLUMN(A1)))
希望这个公式可以帮助你。您需要按Ctrl + shift + enter才能使公式工作,您需要按名称订购PRODUCT列,以使公式正常工作。
= IF(COLUMN() - 4'= COUNTIF($ A $ 2:$ A $ 8,$ D2),INDEX($ B $ 2:$ B $ 8 MATCH($ D2,$ A $ 2:$ A $ 8,0)+ COLUMN() - 5),)
-4正在引用您要查找值的列,-5是要获取值的位置。
列c为空
COL ABCDEFGH
产品说明uniquevalues descr1 descr2 descr3 descr4
oranges 1 oranges 1 2
橘子2苹果3 4 5
葡萄6葡萄6 7
葡萄7
苹果3
苹果4
苹果5
我希望不太晚:P ..欢迎你。
I have an excel formula, which is supposed to work but returns #VALUE and I cannot figure out why.
I have this table:
A B
1 | | |
2 | Oranges | 1 |
3 | Apples | 2 |
4 | Grapes | 3 |
5 | Oranges | 4 |
6 | Apples | 5 |
7 | Grapes | 6 |
8 | Apples | 7 |
I want to Check for matching values in Column A like "Apples", "Oranges", etc. and return all the corresponding values from Column B in one row:
The output should be like this but I only get #VALUE:
A B C D
11 | Apples | 2 | 5 | 7 |
12 | Oranges | 1 | 4 | #NUM|
This is the formula:
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
I hope this formula help you.. you need to press Ctrl + shift + enter to get the formula working, you need to order the PRODUCT column by name to get the formula working properly.
=IF(COLUMN()-4<=COUNTIF($A$2:$A$8,$D2),INDEX($B$2:$B$8,MATCH($D2,$A$2:$A$8,0)+COLUMN()-5),"")
the -4 is referencing the column where you are going to lookup the value and the -5 is where you are going to get the value.
The column "c" is empty
COL A B C D E F G H
PRODUCT DESCRIPTION uniquevalues descr1 descr2 descr3 descr4
oranges 1 oranges 1 2
oranges 2 apples 3 4 5
grapes 6 grapes 6 7
grapes 7
apples 3
apples 4
apples 5
I hope it is not too late :P.. you're welcome.
这篇关于Excel - 从一列中水平返回多个匹配值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!