CODEWITHSUNDEEP
excel-formulaexcel-2010
Matthew Martin
Matthew Martin

Reputation: 29

Excel Array Formula, Multiple criteria

I have a table in which multiple weeks of data is stored and I'm trying to return a value based on 2 criteria. Column A of the data sheet contains the date the report was ran (Always on the same day of the week - 24/05/17, 31/05/17 etc)

I've managed to return the value of column H by using an array formula, based on a cell value (Date) in ''Issues Data Quality Overview'!$B$4' using the following formula:

{=IFERROR(INDEX('Issues Log'!$H$1:$H$20000,SMALL(IF('Issues Data Quality Overview'!$B$4='Issues Log'!$A$1:$A$20000,ROW('Issues Log'!$A$1:$A$20000)-MIN(ROW('Issues Log'!$A$1:$A$20000))+1,""), ROW(A2))),"")}

That returns a value such as "IID-10225-22".

Problem: Now I need to look up that value in the same table, based on a date in another cell, and return column X. (essentially adding the returned value as a criteria).

In all honesty I'm lost as to how I'd do this.

Upvotes: 0

Views: 183

Answers (2)

Matthew Martin
Matthew Martin

Reputation: 29

I've scrapped the array formula, as it really slowed down the processing speed. Instead I've created a Unique ID (=IssueID&Date) and VLookup'd that.

Upvotes: 0

Ricardo Virtudazo Jr
Ricardo Virtudazo Jr

Reputation: 351

@Matthew. I understand your formula gives the list of values in column H with column A values matching 'Issues Data Quality Overview'!$B$4 in an ordered list.

Does your Issues Log, column H have multiple occurrence of the same value? (example: IID-10255-22 can have a value in Issues Log, column A that is not the same value as 'Issues Data Quality Overview'!$B$4).

If not, it doesn't make sense to use the result as a lookup value to get column X, you can simply change the code to:

{=IFERROR(INDEX('Issues Log'!$X$1:$X$20000,...}

If it does have multiple occurrences and you want to get the first occurrence of the result in column H and get the value in column X, best to add a formula right next to your array formula and do a VLOOKUP.

Upvotes: 0

Related Questions