Extract values if conditions are met

**bjnockle** · 08-06-2017, 07:02 PM

Looking for non-array formula (for example LOOKUP Excel Function) to extract values from H3:U19 into J23:M26 if conditions i23:i26 and J22:M22 are met (see Looklist and Outcome Sheet for outcome). Sample file attached.

Also looking for non-array formula (for example LOOKUP Excel Function) to extract values from H3:U19 into J30:M33 if conditions i30:i33 and J29:M29 are met (see Looklist and Outcome Sheet for outcome). Sample file attached.

**leelnich** · 08-06-2017, 08:37 PM

If only ONE row satisfies criteria across multiple columns, SUMIFS can be used instead of a lookup function to return a NUMERIC value.
Try this in J23, copied across and down:
=SUMIFS($U$4:$U$19,$H$4:$H$19,$I23,$I$4:$I$19,J$22)

...and this in J30, copied across and down:
=SUMIFS($U$4:$U$19,$H$4:$H$19,J$29,$I$4:$I$19,$I30)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

**leelnich** · 08-06-2017, 10:03 PM

As noted in post #2, SUMIFS works for NUMBERS, but the following can return ANY value type from one column based on values in other columns:
Alternative for J23, copied across and down:
=LOOKUP(1,1/(($H$4:$H$19=$I23)*($I$4:$I$19=J$22)),$U$4:$U$19)

Alternative for J30, copied across and down:
=LOOKUP(1,1/(($H$4:$H$19=J$29)*($I$4:$I$19=$I30)),$U$4:$U$19)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee