1

Preferring not to use any macros...

I'm trying to build a formula to be able to search for a specific value within a number of different columns. See mock up.

Desired formula picture:

In the yellow boxes I'm searching for a specific number, and to the right I'm wanting to return the heading that value belongs under.

I've tried using a formula including index, small, search but my experience with arrays is not great.

Any thoughts?

Attaching a picture of the formula I'm using now (only including 3 months). I really don't the way this formula is set up because the data import is not easy to replace. I want the data to live in the columns.

Working, bad not desired picture:

Alternatively, if this formula cannot be done, is there any easy way to convert the data from the desired picture into the format of the not desired picture?

Improve this question
1
  • Easily possible with a combination of INDEX, AGGREGATE and COUNTIF or COLUMN. I'll have an asnwer shortly after I retype all of your data. Commented Oct 14, 2017 at 1:05

1 Answer 1

Reset to default
2

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In B14

=IFERROR(INDEX($B$1:$M$1,SMALL(IF($B$2:$M$11=$A14,COLUMN($B$1:$M$1)-COLUMN($B$1)+1),COLUMNS($B14:B14))),"")

and then copy it across and down.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is awesome! I will incorporate it into my project. My project includes over 100 columns so this will be very helpful. Thank you for your time!
@JOney You're welcome! Glad it worked. Please take a minute to accept the answer to mark your question as Solved. :)
One more caveat, is instead of searching could excel automatically populate the values of most instances. I.e. show the top 5 values that occur most frequently?
That's a different question altogether so please open a new question with your new requirement and your attempt to resolve that issue.

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.