Excel Formula to check value of two columns and return specific cell values-CodePudding

I’m trying to create a formula that will check two columns (L and M) for specific values (Active and Disabled) respectively. If Active is found in Column L and Disabled is found in Column M, it gives the Value of Column E.

I’ve tried using nested If statements with index with no luck. I have a table that has over 5k rows I need it to check and spit out the results in a non empty cell table format so vlookup won’t work. I’m at a loss. I’m thinking this will need to be an array but am concerned about the size of the raw data I’m working with.

CodePudding user response：

With Excel 365, you can do:

=FILTER( E:E, L:L&M:M = "ActiveDisabled" )

to give all instances as a spill range.

If there will only be one instance and you want the first instance, you can do:

=INDEX( E:E, MATCH( "ActiveDisabled", L:L&M:M, 0) )

CodePudding user response：

As simple as:

=VLOOKUP(val1&val2,data,column,0)