如下樣表:
姓名在左側數據表中的中間一列,要求在右側查詢區查詢出指定姓名的所有信息,前提是不能改變原表結構。
在這種情況下,很多人首先想到的是VLOOKUP,但此種情況是:姓名在中間,如果查詢“單位”與“職位”,需要逆向查找,而查“性別”與“學歷”,有不需要逆向查詢,這種情況下,很難用VLOOKUP寫出一個公式來解決。
這就需要 INDEX+MATCH組合了。此組合最大的好處在於不需要考慮正向逆向的問題。
公式實現
在H2單元格輸入公式:
=INDEX($A$1:$E$17,MATCH($G$2,$C1:$C17,0),MATCH(H1,$A1:$E1,0))
確定,即可得指定姓名的性別,公式向右填充,可得所有信息。
如下圖:
公式解析
總公式:
=INDEX($A$1:$E$17,MATCH($G$2,$C1:$C17,0),MATCH(H1,$A1:$E1,0))
其中:
MATCH($G$2,$C1:$C17,0) :匹配出G2單元格的姓名在C1:C17區域在第幾行,例如,吳五在第六行,本部分返回值是6;
MATCH(H1,$A1:$E1,0):匹配出H1單元格的“性別”在A1:E1區域在第幾列,本部分返回值是4;
INDEX($A$1:$E$17,MATCH($G$2,$C1:$C17,0),MATCH(H1,$A1:$E1,0)):是在A1:E17區域查找姓名行與性別列交叉點的值,如查詢吳五的性別就是返回第6行與第4列的交叉點的值,即是“女”。
注意:
如果是一次查找好幾個姓名對應的信息,需要修改公式為:
=INDEX($A$1:$E$17,MATCH($G2,$C$1:$C$17,0),MATCH(H$1,$A$1:$E$1,0))
如下圖:
閱讀更多 韓老師講office 的文章