【Excel】指定データが複数ある場合、別の項目での文字列が大きい方を取得したい。

Question

###目標
表1.Idと表2.Idがそれぞれキーとなっていて、下記を基にデータを取得するのが目標です。
・Idを検索値として表2.Idの同じ行のColを取得
・表2.Idで対象データが複数ある場合、項目1の文字列の大きい方を取得

表1

行	Id	ipt
1	1234	[入力箇所]
2	9876
3	6543
4		...etc

--
表2

行	Id	Col
1	1234	AACD
2	9876	ABCD
3	6543	ACCD
4	1234	ADC
5		...etc

######◆前提
★項目1の中で「A」を最小値、「Z」を最大値とする
★文字数はすべて統一して4文字(指定桁になるよう右側空白埋めになっているデータあり→上記の表では行4)

例)
id:1234 → 対象行：行1と行4
取得対象：AACD < ADC となり対象は「ADC」

---

以下の２つの内容は実現できています。
######①文字列の最大値の取得

参照：【Excel】文字列の最大値を取得したい

######②重複するデータがある場合に複数セルにて対象データをすべてを取得

=IFERROR(INDEX($Id$1:$Id$5,MATCH(LARGE(($Id$1:$Id$1="1234")*1/ROW($Id$1:$Id$5),ROWS($Ipt$1:$Ipt1)),1/ROW($Id$1:$Id1),0),COLUMNS($ipt$0:ipt$0)),"")
//ipt列は入力するセルで項目名の行を0行目としています

--
しかし上記コードの場合だと、1セル1データの取得となっておりますが、参照するための列や行を増やしたりすることをせず、入力セルだけで完結させたです。
Excelでの重複データを取得する方法を調べましたが、1セル1データ取得の方法がほとんどでした。

参照するための列や行を増やしたりすることをせず、入力セルだけで完結させたです。

この方法での取得はあるのでしょうか？
わかる方いらっしゃいましたら教えてください。

###スペック情報
OS：Windows10 Pro
Soft：MicrosoftOffice Orifessional Plus 2016 Excel

Comments

[kikukiku]

仕様として不明だと思ったのは下記になります。
空白は最小値でしょうか？最大値でしょうか？
　ABCA　<　ABCZ　＜　ABC
　ABC　<　ABCA　<　ABCZ
表１のidが表２のidに存在しなかったらどうすべきなのか？

[kitasue]

VBAを使っても良いのでしょうか。

[Mr.sijimi]

確認が遅くなってしまい申し訳ありません。

kikukiku様
空白に関しては最小扱いとさせていただいております。
表2に該当データがない場合にはIFERRORを使いnull(式内では"")とさせていただき処理しています。

kitasue様
VBAはできれば使用せず、関数で完結させたいです。

Answer 1

セル値は画像のようにしていますので、ご自身の環境に合わせてください。
B3に下記式を入力、編集状態でCtrl+Shift+Enterを押下してください。

=IF(COUNTIF(D:D,A3)>0,INDEX(E:E,MATCH(MAX((D:D=A3)*COUNTIF(E:E,"<"&E:E)),(D:D=A3)*COUNTIF(E:E,"<"&E:E),0)),"")

Comments

[Mr.sijimi]

ご回答ありがとうございます。
無事目標となるデータが取得できました。

Answer 2

項目1=表2のColでしょうか？
そうであれば、表2をColであらかじめソートしてvlookupを使うのではいかがでしょうか。
(エクセルで単純にソートが出来ない場合も、ソート用計算値の列を作れば可能)

表2にデータを追加するたびにソートが必要なので手間ではありますが、
複雑な式やマクロを導入しても別の人がメンテできなくなるので、
注意書きをでかでかと張り付けてやらせた方がマシかと思います。

追記
使ったことないですが、SORT関数というものがあるそうです。
お使いのバージョンで使用可能であれば、表2をあらかじめソートしておく必要もないかもしれません。
https://mylife8.net/excel-function-sort/

Comments

[Mr.sijimi]

ご回答ありがとうございます。
現状はINDEX関数とMATCH関数を使って取得していて、取得対象の列が左に来ても対応できるようにしていましたが、hoogee様がおっしゃる通り、かなり複雑な式になってしまっております。。。
それから考えると、今回の箇所に関しては簡単なVLOOKUPが優良そうですね。

SORT関数に関してはご提供いただいた情報から少し試してみます。