Share via

Return 1st value in column from an array search

Anonymous
2018-07-24T20:14:34+00:00

I am trying to look through an 8x8 Array that is full of unique words. i want to return the first value in each column based on a search through the array for a specific word. For example, how would i get this to return "Jeff" if I have a search through the entire table looking for the word "Water"

Garrett Sally Mary Sue Tom Jeff
Apple Banana Cherry Blueberry Strawberry Tomato
Onion Melon Grapefruit Orang Grape Rasberry
Blackberry Beef Chicken Pork Beer Water
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. HansV 462.6K Reputation points
    2018-07-24T21:36:51+00:00

    Try this, adjusting the ranges as needed:

    =INDEX($A$1:$H$1,SUMPRODUCT(($A$2:$H$8=K1)*COLUMN($A$1:$H$1)))

    where A1:H8 is the array, and K1 is the cell containing the word you're looking for.

    Was this answer helpful?

    0 comments