Forum Discussion
Table: Formula to return a value from the table if other cells contain certain values
Hi everyone! I have a table (like in the screenshot below) that contains certain values in column D/E/F/G. I want to create a formula in which you can enter in the below B2/B3/B4 values from colu...
=INDEX(G2:G15,MATCH(1,(B2=D2:D15)*(B3=E2:E15)*(B4=F2:F15),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.
I now tried the following for the first few ranges and did a few tests, so far it seems to work, but I wonder if there is a more elegant solution?
=IF(B3<=25999;INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000<=25999)*(B4=F2:F1000);0));IF(AND(B3>25999;B3<=26999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>25999)*(E2:E1000<=26999)*(B4=F2:F1000);0));IF(AND(B3>26999;B3<=39999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>26999)*(E2:E1000<=39999)*(B4=F2:F1000);0));IF(AND(B3>29999;B3<=41999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>39999)*(E2:E1000<=41999)*(B4=F2:F1000);0))))))
=IF(B3<=25999;INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000<=25999)*(B4=F2:F1000);0));IF(AND(B3>25999;B3<=26999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>25999)*(E2:E1000<=26999)*(B4=F2:F1000);0));IF(AND(B3>26999;B3<=39999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>26999)*(E2:E1000<=39999)*(B4=F2:F1000);0));IF(AND(B3>29999;B3<=41999);INDEX(G2:G1000;MATCH(1;(B2=D2:D1000)*(E2:E1000>39999)*(E2:E1000<=41999)*(B4=F2:F1000);0))))))
