Forum Discussion
CatherineMadden
Dec 17, 2024Brass Contributor
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.
- AlphaEraCopper 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.
- cameronaldrichCopper 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.
- mathetesSilver Contributor
Even simpler:
=INDEX($B$1:$F$1,,MATCH(E12,$B$2:$F$2,1))
You don't need rows 3 and 4 at all. Especially since rows 2 and 3 are substantially identical.
Let me recommend that you become familiar with a more complete set of functions for looking up and referencing.
- OliverScheurichGold Contributor
=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.
- CatherineMaddenBrass Contributor
Thank you, it works perfectly!