Displaying minimum and maximum rank across columns in a table

[cwcollins06]

So, I expect this is simpler than I think it is and I'm hoping someone can help me find a solution. I have a table in which I'm recording results from multiple trials in a test. In Column A is the position number 1-150. Each successive column I've recorded the finishing position of each participant. Each participant is represented by a numerical value. What I'm looking for is a formula that will give me the top finishing position for a specific value across all trials, and one that will give me the lowest finishing position for a specific value across all trials. This falls under the category of something I KNOW Excel can do, I just don't know how to make it do it. Thoughts?

[Ron Coderre]

Please post a workbook with some sample data and the results you want to see (based on that data). It may make a huge difference in how we approach a possible solution.

[cwcollins06]

Thanks for the quick reply. Here's a sample workbook. I'd like to see the highest and lowest position for a particular value across the entire table.

[Ron Coderre]

Maybe I'm just being dense...but,
What does Row_1 represent?
What does Col_A represent?
Can you give an example of a particular test?
e.g. Find the highest Col_A(?) value for 15 in Col_B through Col_U. Answer: 19

[cwcollins06]

I doubt you're being dense, I expect I'm just bad at providing everything you need. Column A is the finishing position 1-150, Row 1 is the trial # 1-20.

The example test would be like the one you gave. The highest Col_A value for 16 in Col_B through Col_U. Answer: 2

I'm also looking for the lowest Col_A value for 16 in Col_B through Col_U. Answer: 20

[Ron Coderre]

My apologies for the delay (family reunion)

Using your posted workbook...

Please Login or Register  to view this content.

For your data:
Best = 2
Worst = 20

Note: You could use shorter ARRAY FORMULAs, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

However, nobody I work with ever remembers to CTRL+SHIFT+ENTER when editing them...so, I avoid them.

Is that something you can work with?

[cwcollins06]

That works perfectly, no need to be sorry for the late response! Thanks so much for your help!