SOLVED

# Table: Formula to return a value from the table if other cells contain certain values

Copper Contributor

# 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! :)

8 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Table: Formula to return a value from the table if other cells contain certain values

=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.

# Re: Table: Formula to return a value from the table if other cells contain certain values

In B5:

=IFERROR(INDEX(G2:G1000, XMATCH(1, (D2:D1000=B2)*(E2:E1000=B3)*(E2:E1000=B4)), "")

# Re: Table: Formula to return a value from the table if other cells contain certain values

Oh awesome, thank you so much, that was so quick! Works perfectly :)

# Re: Table: Formula to return a value from the table if other cells contain certain values

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);"")

# Re: Table: Formula to return a value from the table if other cells contain certain values

As variant

``=IFERROR( TOCOL(G2:G1000/(B2=D2:D1000)/(B3=E2:E1000)/(B4=F2:F1000),3), "" )``

# Re: Table: Formula to return a value from the table if other cells contain certain values

Or, with some assumptions

``=XLOOKUP(CONCAT(B2:B4), D2:D1000&E2:E1000&F2:F1000, G2:G1000, "")``

# Re: Table: Formula to return a value from the table if other cells contain certain values

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....

# Re: Table: Formula to return a value from the table if other cells contain certain values

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))))))
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Table: Formula to return a value from the table if other cells contain certain values

=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.