Excel: Index/match to return array

1
votes

Sheet1 & Sheet 2
sheet 1sheet 2

I am looking for a way that can perform the following task in excel for each of the column A values:

  • Match value A2 of sheet 1 to the right value in array A2:C2 from Sheet2 and return the respective array beneath the matching column (in this case A3:A4 from Sheet2).
  • This array would then need to be used for a COUNTIF function for finding value B2 within this returning array.

Sheet1 (Results)
sheet 1-results

1
arraysexcelmatchlookupcountif
Worksheets don't use arrays, perhaps the term you mean is "range"? (Unless you're implying that this should be done via VBA?) Either way, your explanation is a little tough to follow but I suspect you're adding unneeded extra steps and over-complicating a simple task. – ashleedawg
In short I need is to match 1 cell to a 1-row array, and return the column values beneath that matching cell – Loukianos Zavolas
as I mentioned there are no arrays in Excel worksheets. Perhaps it would be helpful if you include representative examples (As opposed to A's and 1's and 0's), and explain what you're starting with, and what you need to end with (without your theory of steps in between like array's or index/match) . Also, as it is I don't know if the "results" image is the "final" output that you need or a transition step, like before/after "the column values beneath that matching cell". – ashleedawg
"For next time" (assuming the answer below works for you) it's much better if your example source data is in a form that can be copied (instead of an image), so others can test their theory in trying to match your desired output. One way would be to use this site with the "compact" setting.) – ashleedawg
Sorry, my explanation was a bit terrible. Thanks for your response. I think I got it now – Loukianos Zavolas

1 Answers

2
votes

Try this in sheet1!c2 and fill down,

=COUNTIF(INDEX(Sheet2!A:C, 0, MATCH(A2, Sheet2!$2:$2, 0)), B2)