Forum Discussion

CatherineMadden's avatar
CatherineMadden
Brass Contributor
Dec 17, 2024

Formula Help IF/AND/XLOOKUP

I have tried an IF formula, and IF/AND formula, and XLOOKUP and nothing is returning the answer I need. I even tried a long way with the XLOOKUP formula. So can someone help me please. I have attached the workbook as well.

 

If a rate is found between rows 3 and 4, then return row 1.

 

  • AlphaEra's avatar
    AlphaEra
    Copper Contributor

    I will use IFS to solve this problem.
    F8=IFS(E8<=$B$4,$B$1,E8<=$C$4,$C$2,E8<=$D$4,$D$1,E8<=$E$4,$E$1,TRUE,$F$1)
    Down fill the other "Amount" cell with the formula above. 

    IFS can solve typical interval problems such as your problem. IFS will stop at the logic test  that first return "true". Therefore, I didn't need to include the lower bond value at row 3.

  • cameronaldrich's avatar
    cameronaldrich
    Copper Contributor

    I like the INDEX/MATCH formulas, but thought for one of my first posts I would also provide a solution using IF/AND and also just IF. In the first example, the formula just uses B1:F2, removing the need for extra 'helper' cells. In the past I struggled with really understanding INDEX/MATCH. IF/AND is still complicated enough but just using IF seems fairly straightforward.

     

  • =INDEX($B$1:$F$1,MATCH(1,($B$3:$F$3<=E8)*($B$4:$F$4>=E8),0))

    This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

    In cell H12 there isn't a value so far because cell F4 is empty. If you enter e.g. 500 in cell F4 then "$70" is returned in cell H12.

     

Resources