EXCELの関数探してます、lookupのような

質問

A列に1,10,20,30とありB列に200,300,400,500とある場合を想定します
LOOKUP関数を使い、1のときは200、10の時300,20の時400のように値を出したいとします。つまり横の値。

LOOKUP関数を使うと2,3,4,5,6,7,8や11,12,13~19などの検査値で不完全一致の場合の値を出すとき、
2,3,4,5,6,7,8のときは1の値の200がでて、11,12,13~19の時は10の値の300が出ますよね
ようはその値より上の値の数字をだしてくれるんです。14なら300,26なら400,39なら500のように

質問は下の値を出してくれるLOOKUPのようなものはありませんか？14なら400,26なら500のような

回答No.6

kagakusuki · ベストアンサー

もし、A列の値が、昇順でも降順でもなく、順不同に並んでいる場合には、E1セルに入力する関数を次の様なものとして下さい。

=INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&$D1)+1),$A:$A,0))

　只、このままでは、検索値を入力するセルが空欄となっている場合や文字列が入力されている場合には、A列に最小値が入力されている行のB列の値（御質問文中の例では200）が表示されてしまいますし、検索値がA列に入力されている値の最大値（御質問文中の例では30）を上回っている場合には、#NUM!エラーとなってしまいますから、これを防ぐためには次の様にします。

=IF(OR($D1="",$D1>MAX($A:$A)),"",INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&$D1)+1),$A:$A,0)))

　又、D列やE列のセルに対して、セルの切取り・挿入・削除等を行っても、正しい結果が表示される様にするためには、次の様にします。

=IF(OR(INDEX($D:$D,ROW())="",INDEX($D:$D,ROW())>MAX($A:$A)),"",INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&INDEX($D:$D,ROW()))+1),$A:$A,0)))

お礼

すごい！その一言に尽きる。

回答No.5

kagakusuki

もし、A列の値を、降順ではなく、どうしても昇順に並べなくてはならない場合には、E1セルに入力する関数を次の様なものとして下さい。

=INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&$D1)+1),$A:$A))

　只、このままでは、検索値を入力するセルが空欄となっている場合や文字列が入力されている場合には、A列に最小値が入力されている行のB列の値（御質問文中の例では200）が表示されてしまいますし、検索値がA列に入力されている値の最大値（御質問文中の例では30）を上回っている場合には、#NUM!エラーとなってしまいますから、これを防ぐためには次の様にします。

=IF(OR($D1="",$D1>MAX($A:$A)),"",INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&$D1)+1),$A:$A)))

　又、D列やE列のセルに対して、セルの切取り・挿入・削除等を行っても、正しい結果が表示される様にするためには、次の様にします。

=IF(OR(INDEX($D:$D,ROW())="",INDEX($D:$D,ROW())>MAX($A:$A)),"",INDEX($B:$B,MATCH(SMALL($A:$A,COUNTIF($A:$A,"<"&INDEX($D:$D,ROW()))+1),$A:$A)))

回答No.4

kagakusuki

何度も申し訳御座いません。
　ANo.2において最初に挙げた関数は、そのままでも何ら問題なく正常に動作致しますが、セルの切取り・挿入・削除等を行う事に対応するための工夫が、中途半端に（不完全に）応用されている形となっていて、関数の基本的な考えを理解する際の妨げになるかも知れませんので、基本的な考え方を理解するためには、ANo.2で１番目に挙げた関数を、次のものと差し替えて下さい。

=INDEX($B:$B,MATCH($D1,$A:$A,-1))

　そして、ANo.2で2番目に挙げた関数は、次のものと差し替えて下さい。

=IF(OR($D1="",$D1>MAX($A:$A)),"",INDEX($B:$B,MATCH($D1,$A:$A,-1)))

　尚、3番目に挙げた関数に関しましては、帝政箇所は御座いません。

回答No.3

KURUMITO

例えばＤ列に２，３，４，１４・・・のように数値が有るとしたらＥ１セルには次の式を入力して下方にドラッグコピーします。

=INDEX(B:B,MATCH(D1,A:A,1)+1)

回答No.2

kagakusuki

ANo.1です。
　もしう訳御座いません。先程の回答の関数では、検索値がA列の最小値（御質問文中の例では1）よりも小さな値の場合には、#N/Aエラーとなってしまう事に後から気付きました。
　それに、少々寝ぼけていた様で、もっと簡単な方法が御座いました。

　まず、A列の値を上から順に、1,10,20,30ではなく、30,20,10,1の様に降順に並べ替えて下さい。
　そして、E1セルに入力する関数は以下の様なものにして下さい。

=INDEX($B:$B,MATCH(INDEX($D:$D,ROW()),$A:$A,-1))

　只、このままでは、検索値を入力するセルが空欄となっている場合には、A列に最小値が入力されている行のB列の値（御質問文中の例では200）が表示されてしまいますし、検索値がA列に入力されている値の最大値（御質問文中の例では30）を上回っている場合には、#N/Aエラーとなってしまいますから、これを防ぐためには次の様にします。

=IF(OR($D1="",$D1>MAX($A:$A)),"",INDEX($B:$B,MATCH(INDEX($D:$D,ROW()),$A:$A,-1)))

　又、D列やE列のセルに対して、セルの切取り・挿入・削除等を行っても、正しい結果が表示される様にするためには、次の様にします。

=IF(OR(INDEX($D:$D,ROW())="",INDEX($D:$D,ROW())>MAX($A:$A)),"",INDEX($B:$B,MATCH(INDEX($D:$D,ROW()),$A:$A,-1)))

回答No.1

kagakusuki

今仮に、A列の値は必ず昇順に並んでいて、2,3,4,5,6,7,8や11,12,13~19などの検査値がD列に並んでいて、検索結果をE列に表示するものとします。
　その場合、E1セルに入力する関数の一例は、

=INDEX($B:$B,MATCH($D1,$A:$A)+(COUNTIF($A:$A,$D1)=0))

となります。
　只、このままでは、検索値を入力するセルが空欄であったり、検索値がA列に入力されている値の最大値である500を上回っている場合には、#N/Aエラーとなってしまいますから、これを防ぐためには次の様にします。

=INDEX($B:$B,MATCH($D2,$A:$A)+(COUNTIF($A:$A,$D2)=0))

　又、D列やE列のセルに対して、セルの切取り・挿入・削除等を行っても、正しい結果が表示される様にするためには、次の様にします。

=IF(OR(INDEX($D:$D,ROW())="",INDEX($D:$D,ROW())>MAX($A:$A)),"",INDEX($B:$B,MATCH(INDEX($D:$D,ROW()),$A:$A)+(COUNTIF($A:$A,INDEX($D:$D,ROW()))=0)))