Forum Discussion
Solved
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 columns D, E and F and depending on the row in the table where all 3 values are true, then B5 returns the corresponding value from column G in that row.
Does anyone have any idea which formulas I could use for this? Thank you so much for your ideas! 🙂
=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.
mates... done I found it, here you have what I used and it's working...
=IFERROR(XLOOKUP(L4;'Skills Evaluation'!$E$21:$BZ$21;('Skills Evaluation'!$E$22:$BZ$22/'Skills Evaluation'!$E$22:$BZ$22);"";0;1);"")
- 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)))))) - Thank you so much for all your help! One more challenge, it should now do the same, but B3 can now be a value in a certain range. So if B3 is <= 25999 it should return the value in G that belongs to the combination with 25999, if B3>25999&<=26999 it should return the value in G that belongs to 26999, if B3>26999&<=36999 it should return the value in G that belongs to 36999 and so on.
Any ideas? I played around a lot with "ifs" and "ands" but no luck yet.... - Tried, I made 2 small adjustments and that works perfectly as well, thank you! 🙂 =IFERROR(INDEX(G2:G1000;XMATCH(1;(D2:D1000=B2)*(E2:E1000=B3)*(F2:F1000=B4));0);"")
Or, with some assumptions
=XLOOKUP(CONCAT(B2:B4), D2:D1000&E2:E1000&F2:F1000, G2:G1000, "")
=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.
- Oh awesome, thank you so much, that was so quick! Works perfectly 🙂
Dears...help :-)
I have one work sheet where I have a table and values with several names, so whenever it will be in line x a specific value and then in line X+1 another value I need to return a value, please
I need to know the plan "Line 12" if per month it was done or not "Line 13" with values, example for "Jun" was planned 4 and was done 4 so 100% covered
I really appreciate your help
