0

I have a list of data that I want to pull particular data out of based off a letter in one column OR a number in another. I've set up two different array formulas to pull the data I want, but I want to make one list with all the data but I can't figure out how to make one formula that will pull the data that EITHER matches a letter in one column OR that mathces a number in different column.

Here's the formula that I have that pulls two different lists that I want to have as one list:

In A2

=IFERROR(INDEX('Raw Data'!$A$3:$M$2548, SMALL(IF(COUNTIF('Raw Data'!$A$1,'Raw Data'!$F$3:$F$2548), MATCH(ROW('Raw Data'!$A$3:$M$2548), ROW('Raw Data'!$A$3:$M$2548)), ""), ROWS($A$2:F8)),COLUMNS('Raw Data'!$A$3:F9)),"")

In O2

 =IFERROR(INDEX('Raw Data'!$A$3:$M$2548, SMALL(IF(COUNTIF('Raw Data'!$G$1,'Raw Data'!$G$3:$G$2548), MATCH(ROW('Raw Data'!$A$3:$M$2548), ROW('Raw Data'!$A$3:$M$2548)), ""), ROWS($O2:O$2)),COLUMNS('Raw Data'!$A$3:A3)),"")

How can I combine those two in order to get one list instead of two seperate lists?

Here's a sample of the raw data:

raw data

How I'd like to see it come out:

Like This

Improve this question
3
  • have you tried using SUMPRODUCT?
    – Alex M
    Commented Feb 19, 2020 at 23:46
  • Hi @Ben H. better share some sample data along with expected output, help us to fix the issue !!
    – Rajesh Sinha
    Commented Feb 20, 2020 at 7:08
  • Done, added data
    – Ben H.
    Commented Feb 21, 2020 at 15:33

1 Answer 1

Reset to default
1

Use AGGREGATE instead of SMALL(IF(MATCH( to supply the row number into INDEX.

=INDEX('Raw Data'!A:A, AGGREGATE(15, 7, ROW($3:$2548)/SIGN(('Raw Data'!F$3:F$2548=F$1)+('Raw Data'!G$3:G$2548=G$1)), ROW(1:1)))

enter image description here

Improve this answer
3
  • If you want to drag it across for additional columns then you probably have to change part of the formula to SIGN(('Raw Data'!$F$3:$F$2548=$F$1)+('Raw Data'!$G$3:$G$2548=G$1)). A cursory examination of the formulas that didn't work should have shown that the columns were shifting.
    – user385793
    Commented Feb 21, 2020 at 17:56
  • I actually figured that out after my comment. Thanks. However, what if I wanted to be able to sort and filter the results?
    – Ben H.
    Commented Feb 21, 2020 at 18:56
  • That would be better as a new question as it raises the possibility that Advanced Filter or an SQL query might be more suited to the task at hand.
    – user385793
    Commented Feb 21, 2020 at 19:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .