最近發現除了 VLOOKUP (連結)

INDEX+MATCH 也能從一份密密麻麻的 EXCEL 表格中,搜尋特定資料。

 

  • 教學如下:

 

MATCH( ) 函數:

[找位置]

MATCH中譯:符合

功能:確認指定範圍中是否有我們想要的值,與存在的位置。

公式:

= MATCH(, 哪裡找, 比較方式)


比較方式:

1:找比需求較小的數值

0:找完全相同的值

-1:找比需求較大的數值

 

【範例】

下表為牛氓兄妹(布魯斯&嬌嬌)的〝喜愛名單〞。

※ 以下以範圍(哪裡找)【橫向】示範。

A

B

C

D

E

F

G

H

1

 

1

2

3

4

5

6

7

2

布魯斯

逗貓棒

貓跳台

紙箱

雷射筆

橡皮筋

老鼠娃娃

軌道球

3

嬌嬌

老鼠娃娃

雷射筆

紙箱

橡皮筋

貓跳台

逗貓棒

軌道球

 

【目標】

尋找布魯斯的〝喜愛名單〞裡,有沒有雷射筆?

註:目標為文字時,公式以 "文字" 表示。

 

 

J

K

L

M

N

1

 

目標

公式

結果

意思

2

 

布魯斯

雷射筆

=MATCH("雷射筆",A2:H2,0)

5

雷射筆在布魯斯那列的第5

 

=MATCH(K2,A2:H2,0)

 

【目標】

尋找布魯斯的〝喜愛名單〞裡,有沒有跳繩?

 

 

J

K

L

M

N

1

 

目標

公式

結果

意思

2

 

布魯斯

跳繩

=MATCH("跳繩",A2:H2,0)

#N/A

跳繩不在布魯斯的喜愛名單裡

 

=MATCH(K2,A2:H2,0)

 

INDEX( ) 函數:

[找內容]

中譯:索引

功能:傳回搜尋範圍中第 N 列,第 M 欄的內容

公式:

= INDEX(哪裡找, N , M )

因為搜尋表格時,我們常無法得知搜尋目標是第幾列,所以常與MATCH搭配,如下:

= INDEX(哪裡找, Match(, 含誰的那欄, 比較方式), 需求欄位)

 

【範例】

下表為牛氓兄妹(布魯斯&嬌嬌)的〝喜愛名單〞。

 

A

B

C

D

E

F

G

H

1

 

1

2

3

4

5

6

7

2

布魯斯

逗貓棒

貓跳台

紙箱

雷射筆

橡皮筋

老鼠娃娃

軌道球

3

嬌嬌

老鼠娃娃

雷射筆

紙箱

橡皮筋

貓跳台

逗貓棒

軌道球

 

【目標】

尋找布魯斯的〝喜愛名單〞裡,第6名是?

尋找嬌嬌的〝喜愛名單〞裡,第3名是?

 

 

J

K

L

M

1

 

目標

公式

結果

2

 

布魯斯

第 6 名

=INDEX(A:H,MATCH(J2,A:A,0),7)

老鼠娃娃

 

=INDEX(喜愛名單,MATCH(J2,A:A,0),7)

3

 

嬌嬌

第 3 名

=INDEX(A:H,MATCH(J3,A:A,0),4)

紙箱

 

=INDEX(喜愛名單,MATCH(J4,A:A,0),4)

 

註:公式內的〝喜愛名單〞是我為A1:H3定義的範圍名稱。

定義範圍名稱方式如下:

(可省略,但建議執行,以加快之後的公式設定,並可跨工作表使用。)

STEP1. 圈選搜尋範圍。

STEP2. 按滑鼠右鍵→選擇 定義名稱(A)

STEP3. 定義搜尋範圍名稱。

STEP4. 設定公式時以鍵盤F3叫出範圍。

 

比較起來INDEX+MATCH

  • 優點是:較輕易操作,不像VLOOKUP規矩較多。
    VLOOKUP限定搜尋條件(),一定要在範圍的第一欄。

 

  • 缺點是:公式比較難記,書到用時,真的要估哥一下…  

文章標籤

excel index match

全站熱搜

小凱蘿 發表在 痞客邦 留言(3) 人氣()