工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
❸然後使用SMALL函數,找到第1小的數字,第2小的數字,第3小的數字....以此類推
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
❸然後使用SMALL函數,找到第1小的數字,第2小的數字,第3小的數字....以此類推
❹最後用Index函數,根據這些得到的索引序列號2,4,6,我們要得到的結果是姓名的話,也就是找到A列的第2個,第4個,第6個...就是我們的結果:
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
❸然後使用SMALL函數,找到第1小的數字,第2小的數字,第3小的數字....以此類推
❹最後用Index函數,根據這些得到的索引序列號2,4,6,我們要得到的結果是姓名的話,也就是找到A列的第2個,第4個,第6個...就是我們的結果:
A列的第1024個數字為空,所以顯示成了0
❺最後用&"",讓這些數字0不顯示出來,就得到了最後我們需要的結果:
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
❸然後使用SMALL函數,找到第1小的數字,第2小的數字,第3小的數字....以此類推
❹最後用Index函數,根據這些得到的索引序列號2,4,6,我們要得到的結果是姓名的話,也就是找到A列的第2個,第4個,第6個...就是我們的結果:
A列的第1024個數字為空,所以顯示成了0
❺最後用&"",讓這些數字0不顯示出來,就得到了最後我們需要的結果:
所以這個萬金油公式,你理解了麼?
如果我們用INDEX+LARGE+IF也可以進行求解:
=IFERROR(INDEX(A:A,LARGE(IF($C$1:$C$6=$A$10,ROW($C$1:$C$6),-1000),ROW(A1))),"")
工作中vlookup函數更多用於一對一的查詢,如果碰到了一對多的情況,經常會看到使用index+small+if函數公式
例如左右是基礎信息,然後我們列出一個部門,需要把部門成員全部找出來,則可以使用這個一對多的萬金沒公式:
=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&""
輸入完公式按CTRL+SHIFT+ENTER三鍵進行計算
很多文章中都出現了這個公式,但很少有解釋為什麼這麼用的,我們來進行思路理解
❶首先我們查找的是市場部,所以首先組成一個數組,得到所有是市場部的列序號
使用公式一:$C$1:$C$6=$F$2,得到的結果是:
❷然後我們要保留得到TRUE的結果,FALSE的結果讓它變得無限大,我們這裡使用的2的10次方,也就是1024,當然可以修改的更大,比如2的100次方
使用IF(公式一,row($C$1:$C$6),2)
❸然後使用SMALL函數,找到第1小的數字,第2小的數字,第3小的數字....以此類推
❹最後用Index函數,根據這些得到的索引序列號2,4,6,我們要得到的結果是姓名的話,也就是找到A列的第2個,第4個,第6個...就是我們的結果:
A列的第1024個數字為空,所以顯示成了0
❺最後用&"",讓這些數字0不顯示出來,就得到了最後我們需要的結果:
所以這個萬金油公式,你理解了麼?
如果我們用INDEX+LARGE+IF也可以進行求解:
=IFERROR(INDEX(A:A,LARGE(IF($C$1:$C$6=$A$10,ROW($C$1:$C$6),-1000),ROW(A1))),"")
它的求解是從下向上的求出所有結果。
所以你學會了麼?歡迎留言討論~