CODEWITHSUNDEEP
excelconditional-statements
Ron Wendt
Ron Wendt

Reputation: 1

Excel formula to return a value from a range where conditional statements are true

I've searched for an answer for this for awhile now but haven't had any luck. It's a bit difficult to put into words, so please let me know if anything's unclear.

What I want is a way to perform conditional statements on two columns. When both conditions are true, I want to return the value from a third column that is in the same row as where these conditions were met. So if the conditions are true in D5 and C5, I want to return the value from Q5 into the cell with the formula. The conditional statements are easy enough but I haven't figured out the last step yet.

If I use the following formula, I get 1 in the cell with the formula, which is right.

=IF(AND('Stata Output'!D2:D6='Backcheck schedule and results'!C2, 'Stata Output'!C2:C6=1),1,0)

However, replacing 1 in the formula with the range of cells that I want to pull the value from gives me a zero every time, which I know is wrong. For this latter formula I'm using:

=IF(AND('Stata Output'!D2:D6='Backcheck schedule and results'!C2, 'Stata Output'!C2:C6=1),'Stata Output'!Q2:Q5,0)

Any ideas on where I'm messing up? Thanks!

Upvotes: 0

Views: 21111

Answers (3)

barry houdini
barry houdini

Reputation: 46401

Try the following formula:

=LOOKUP(2,1/('Stata Output'!D2:D6='Backcheck schedule and results'!C2)/('Stata Output'!C2:C6=1),'Stata Output'!Q2:Q5)

If there is more than one row where both conditions are satisfied, this formula will return the col Q value from the last of those.

Upvotes: 0

Michael
Michael

Reputation: 379

Looks like AND isn't working correctly in array formulas, so I would use this:

=IF(('Stata Output'!D2:D6='Backcheck schedule and results'!C2)*('Stata Output'!C2:C6=1)=1,'Stata Output'!Q2:Q5,0)

This will give you a new results column, that will be filled from the Q2:Q6 column for every matching row.

To use it, you should select all of the results column, input the formula and then use CTRL+SHIFT+ENTER.

Just make sure to use use ranges of the same size.

Upvotes: 0

RocketDonkey
RocketDonkey

Reputation: 37279

Give this a shot - I'm sure there is a simpler way but it seems to get the job done (make sure to enter as an array formula with Control+Shift+Enter):

=INDEX('Stata Output'!$D$2:$Q$6,MATCH(1,(--('Stata Output'!$C$2:$C$6=1)*--('Stata Output'!$D$2:$D$6="Backcheck schedule and results")),0),14)

What this does is take your entire table as the range (first argument to INDEX, then uses MATCH to find the row where both of your conditions are met (in this case, column C must be 1 and the corresponding value in column D must be Backcheck schedule and results). You then search the resulting array for 1 (which indicates the match) and return column 14 (which represents column Q in our table). You can continue to add filters inside the MATCH() function as needed.

Note that this will match only the first 'hit'.

Upvotes: 1

Related Questions