VLOOKUP()関数の左側を取得したい場合の方法
使用する関数:OFFSET、MATCH、VLOOKUP、VALUE、TODAY
・関数 | VLOOKUP(ブイルックアップ) |
・書式 | =VLOOKUP(検索値,範囲,列番号,検索の型) |
・内容 | 指定された範囲の左端の列で特定の値を検索し、範囲内の対応するセルの値を返します。 |
サンプル
|
A |
B |
C |
D |
E |
F |
1 |
|
|
|
|
|
|
2 |
|
本日の担当者 5月25日 |
高橋 |
6789 |
10:00~19:00 |
|
3 |
|
|
|
|
|
|
4 |
|
日時 |
担当 |
内線 |
受付 |
|
5 |
|
5月23日 |
鈴木 |
1234 |
9:00~18:00 |
|
6 |
|
5月24日 |
佐藤 |
5678 |
10:00~19:00 |
|
7 |
|
5月25日 |
高橋 |
6789 |
10:00~19:00 |
|
8 |
|
5月26日 |
鈴木 |
1234 |
9:00~18:00 |
|
9 |
|
5月27日 |
鈴木 |
1234 |
9:00~18:00 |
|
10 |
|
5月28日 |
高橋 |
6789 |
9:00~18:00 |
|
11 |
|
5月29日 |
鈴木 |
1234 |
9:00~18:00 |
|
12 |
|
5月30日 |
鈴木 |
1234 |
9:00~18:00 |
|
13 |
|
5月31日 |
佐藤 |
5678 |
10:00~19:00 |
|
14 |
|
6月1日 |
佐藤 |
5678 |
10:00~19:00 |
|
15 |
|
6月2日 |
高橋 |
6789 |
9:00~18:00 |
|
16 |
|
6月3日 |
小林 |
3456 |
9:00~18:00 |
|
17 |
|
|
|
|
|
|
セルB2)
="本日の担当者 "&MONTH(TODAY())&"月"&DAY(TODAY())&"日"
セルC2)担当者を呼び出す
=VLOOKUP(VALUE(TODAY()),B5:E16,2,0)
セルD2)内線番号を呼び出す
=VLOOKUP(VALUE(TODAY()),B5:E16,3,0)
セルE2)受付時間を呼び出す
=VLOOKUP(VALUE(TODAY()),B5:E16,4,0)
ここまでがVLOOKUP()関数の普通の使い方です
それでは内線番号から担当者を呼び出すには?
内線より担当が左側にあります
B3からE14の範囲をD3からE14にして-1してみます
=VLOOKUP(VALUE(TODAY()),D3:E14,-1,0)
もちろんエラー(VALUE!)です
VLOOKUP()関数では列番号が範囲を超えるとエラー(VALUE!)になります
VLOOKUP()関数では左側の取得は無理なんです。あきらめましょう。
左側の取得するにはOFFSET()関数とMATCH()関数を組み合わせます
OFFSET()関数とMATCH()関数を組み合わせる方法
例)内線から担当者を呼び出す場合
=OFFSET(
D4,
MATCH(C2,C5:C16,0),
-1)
D4のセルを基準に担当者を
C5からC16を検索して
何行目か取得して
1列戻しています
詳細
=
MATCH(C2,
C5:C16,0)
担当者を
C5からC16を
何番目か検索しています
=OFFSET(
D4,3,
-1)
D4のセルを基準セルとして、3行目、-1列目を取得しています
VLOOKUP()関数では範囲を指定するので範囲内しか呼び出せませんが
OFFSET()関数とMATCH()関数の組み合わせはマイナスで左側も呼び出せます
OFFSET()関数とMATCH()関数で右側を取得することもできます
=VLOOKUP(VALUE(TODAY()),B5:E16,2,0)
=OFFSET(B4,MATCH(VALUE(TODAY()),B5:B16,0),1)
上記は2つとも同じ結果になります
以上 VLOOKUP()関数の左側を取得したい場合でした
OFFSET()関数とMATCH()関数の使用例
MAXIFS(マックスイフエス)とMINIFS(ミニマムイフエス)
新しいエクセルとExcel ONLINE では「VLOOKUP」と似た新しい関数「XLOOKUP」が追加されました
「XLOOKUP」ならば「VLOOKUP」と同じような使い方で左側を取得することが可能になりました
参考:
XLOOKUP(エックスルックアップ)