# Vertical and horizonal search

1. ## Vertical and horizonal search

Hi all
I attached an example of what I am looking for as it is rather difficult to explain.

I got a matrix with on top a row of names of persons.
On the column left of the matrix dates are given.
In between are codes.

I am looking for a formula that collects all persons that have one of the codes given in cells L5:L8 on a particular date which is given in cell L9

The list will start at L11 downwards.

2. ## Re: Vertical and horizonal search

Well, your references didn't match the attached workbook, so I used what
was in the workbook. My edited version is attached.

The base formula for listing matching names is:

``Please Login or Register  to view this content.``

Copy L11 down through L13.

Note: That formula lists the names in the reverse order of occurrence. I hope that's OK.

Does that help?

3. ## Re: Vertical and horizonal search

Ron this is awesome. Just what I was looking for.
I tried the formula on my own sheet, but got a strange problem.
Instead of giving the names of persons correlating to the given date it collects the names of persons of the day after the given date.

ie. When 25-sep is entered as reference it collects the names of people from the 26th...

Btw I don't get the \$11:11 at the end of the formula..?

Here is your code used on my sheet.

``Please Login or Register  to view this content.``

4. ## Re: Vertical and horizonal search

Here is an example of the sheet

5. ## Re: Vertical and horizonal search

There was a minor flaw in your formula. The OFFSET function's initial range
was in Row_1. The MATCH function's data began in Row_3. Consequently, we
need to account for the Row_2 gap by adding 1 to the RowOffset value:

``Please Login or Register  to view this content.``

Copy that formula down as far as you need.

Regarding the ROWS(\$47:47)....
When the formula is copied down, that part only generates an incrementing
series of numbers (1, 2, 3, 4, etc). The LARGE function uses those values
to return the 1st largest column number, 2nd largest column number, etc.

I hope that helps.

6. ## Re: Vertical and horizonal search

Thanks Ron, I am now going to give it a try.
What about the blabla1-part why do they appear in the list?
Is there a way to hide them?

7. ## Re: Vertical and horizonal search

Originally Posted by Jonathan78
Thanks Ron, I am now going to give it a try.
What about the blabla1-part why do they appear in the list?
Is there a way to hide them?
I didn't notice the incorrect returned values.
Try this:
``Please Login or Register  to view this content.``
Copy that formula down as far as you need.

When the formula runs out of matched items, it returns the value of cell O1,
the blank part of a merged cell.

8. ## Re: Vertical and horizonal search

Ron, that worked. I canīt thank you enough.

9. ## Re: Vertical and horizonal search

You're very welcome....I'm glad I could help.

10. ## Re: Vertical and horizonal search

Hello RC,

I have an almost similar situation as the one we discussed before, but can't ament your formula to fit the new situation.

Would you mind to take a look at it.

[I started an almost similar thread on the following forum]

11. ## Re: Vertical and horizonal search

1) Thanks for referencing the other forum's activity.
2) This post has been inactive for a few months. A new thread would have been more appropriate.
3) Even though you linked to the other forum, you should still document your issue. It would have been nice to mention that you swapped row and colum headings in your new grid.

4) Using your posted workbook, put this formula on sheet "b":

``Please Login or Register  to view this content.``
Copy that formula down through C15.

Does that help?

12. ## Re: Vertical and horizonal search

Thank you so much RC.
I couldn't do it myself.
I still have a lot to learn

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1