1

Sheet1 & Sheet 2
sheet 1 sheet 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

Improve this question
7
  • 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. Commented Jun 3, 2018 at 21:49
  • In short I need is to match 1 cell to a 1-row array, and return the column values beneath that matching cell Commented Jun 3, 2018 at 22:04
  • 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". Commented Jun 3, 2018 at 22:14
  • 1
    "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.) Commented Jun 3, 2018 at 22:16
  • 1
    Sorry, my explanation was a bit terrible. Thanks for your response. I think I got it now Commented Jun 3, 2018 at 22:21

1 Answer 1

Reset to default
2

Try this in sheet1!c2 and fill down,

=COUNTIF(INDEX(Sheet2!A:C, 0, MATCH(A2, Sheet2!$2:$2, 0)), B2)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

"...or, maybe Jeeped will come along and effortlessly find clarity in the mud." (Must be a 4x4'er)
@Jeeped I should have guessed. I've spent plenty of time on that site. ...and plenty more on the muddy V.I. trails.
@Jeeped - it took me a sec to realize what you meant but now I picture it on on your vehicle - I now assume your 4x4 is labelled with the nickname. Ashlee was my dog's name and stuck for different reasons, but it's interesting to see how I'm treated differently by people assuming I'm female: sometimes better, sometimes worse, sometimes they will not leave me alone. I was even proposed to, on Valentine's Day, on an MS Access forum, by a man in India.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.