查找Excel公式以获取多个条件 [英] Lookup Excel formula for multiple criteria

问题描述

我有这个问题:获取阈值量.请帮忙 要获取阈值量，我需要遵循以下矩阵:

I have this problem: get the threshold amount. please help to get the threshold amount i need to follow the below matrix:

Tenor           Notional         Threshold
up to 1 yr      up to 500k         .05
up to 1 yr      500k to 1mio       .75
up to 1 yr      1mio above          .8
1-5yr           up to 500k         .1
1-5yr           500k to 1mio       .25
1-5yr           1mio above          .5
5-above          up to 500k         .3
5-above           500k to 1mio       .25
5-above           1mio above          .5   

推荐答案

您可以采用这种方式，我假设您可以添加一个辅助列(蓝色)，其中Tenor和Notional值被串联在一起以进行匹配工作.

You can approach it this way, I have assumed you can add a helper column (in blue) where the Tenor and Notional values are concatenated together for the match to work.

单元格A2和B2具有数据验证列表，因此已选择目标.将显示结果单元格和串联的公式.

Cells A2 and B2 have data validation lists so the target is chosen. The formulae is shown for both the result cell and the concatenation.

请参阅:

这篇关于查找Excel公式以获取多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！