CODEWITHSUNDEEP
excel
Kyle
Kyle

Reputation: 1173

Return array of values based on criteria

I am attempting to create an Excel formula to return an array of values based on logic.

I am aware of Excel functions like SUMIFS that accept parameters for range and condition, but instead of returning a sum of range, I want to return a (cell overflow) array of values based on condition.

This answer helped, but I do not want the return array to be sorted. I need the array indexes to be the same as if they were filtered.

Example cells:

Name     Type        Shares      Price
STA      Buy         12          5.7
STA      Buy         15          3.8
STA      Buy         100         5.4
STA      Sell       -50          5.8
GA       Buy         55          3.55

Current formula:

# works but sorted least to greatest, want to return non sorted
=SMALL(IF((NameRange="STA")*(TypeRange="Buy"),PriceRange),ROW(INDIRECT("1:"&COUNTIFS(NameRange,"STA", TypeRange,"Buy"))))

This returns:

3.80
5.40
5.70

I am attempting to return non-sorted, as in original display order of matches:

5.7
3.8
5.4

Upvotes: 0

Views: 3321

Answers (1)

urdearboy
urdearboy

Reputation: 14590

If you have access to Microsoft 365 you can use FILTER to produce your output:

=FILTER(D2:D6,(A2:A6=H3)*(B2:B6=H4))

enter image description here

You could also return your values in a comma seperate string by nesting the above formula in TEXTJOIN(", ", TRUE, [FORMULA ABOVE]) which would yield 5.7, 3.8, 5.4

Upvotes: 2

Related Questions