Index/match?

[slam]

Think I need an INDEX/MATCH for this, but don't really know how to form it.....

I am trying to create a formula in K3 that says:

If B2 = Yes, and B9 = 25%, match K2 to Circuits B2:E993, and display the corresponding value from column E
else
If B2 = Yes, and B9 = 50%, match K2 to Circuits B2:F993, and display the corresponding value from column F
else
If B2 = Yes, and B9 = 100%, match K2 to Circuits B2:G993, and display the corresponding value from column G
else
If B2 = No, display blank

Any help would be greatly appreciated. Thank you!

 

[Aladin Akyurek]

Looks like:

=IF(B2="Yes",INDEX(Circuits!E:G,MATCH(K2,Circuits!B:B,0),MATCH(B9,{0.25,0.5,1},0)),"")

 

[slam]

Perfect, thank you so much!

EDIT: Bit of a related post to apply this formula a different way - thought the user might feel a bit confused if they have to overwrite it if B2 is not yes: http://www.mrexcel.com/forum/excel-...range-cells-if-condition-met.html#post4177700

 

[Aladin Akyurek]

Perfect, thank you so much!

You are welcome.

EDIT: Bit of a related post to apply this formula a different way - thought the user might feel a bit confused if they have to overwrite it if B2 is not yes: http://www.mrexcel.com/forum/excel-...range-cells-if-condition-met.html#post4177700

Is this a follow up on my suggestion above?

 

[slam]

You are welcome.



Is this a follow up on my suggestion above?

Somewhat, but it's a different question, so I created a new thread. Instead of including the If "Yes" condition in the formula, I want to include that in some VBA, and only add the INDEX/MATCH formula to the cells when the Yes condition is met, otherwise the cells wouldn't include the formula.