Thread: Listing the Highest Common Value in a Table

I used to fancy myself quite the Excel adept until I ran into this problem. 90 minutes later and I'm no closer to solving it. What I am trying to do is simply compare the numerical values of multiple columns in a table and find the highest value number that occurs in all columns. It's basically a cross between GCD() or MAX() and MATCH() but I need it to work in an array/index fashion and the number has to be present in every column of the table. For example:

     A     B     C     D     E
1    2     4     12    20    1
2    3     8     15    24    4
3    4     12    18    28    9
4    6     16    21    32    14
5    24    18    24    36    19
6    36    24    27    40    24

So, out of that example, I need to get "24" as a result as it occurs in every column, and where it occurs row-wise isn't a concern at this point.

Thanks in advance for reading!

Thanks for the quick reply, teylyn. Unfortunately, that doesn't accomplish what I need. When I evaluate that formula, I see that what it's doing is only finding the lowest value of the maximum values from each column, and the formula evaluates out to MIN(36,24,27,40,24). That example was probably poor but the data with which I'm dealing is too large to post here. I'll try and simplify what it is.

I have a table that's setup to find the largest common number between 1 and 1200 that is divisible evenly (i.e., whole integers) by 2, 3, 4, 5, 6, 8, and 10, excluding 1200 itself. So, I have seven columns headed by each of those numbers, and then I have an index column, numbered 1 through 600 to cover the full spread of multiples up to 1200. The solution is 1080, which is present in all seven factor columns, however the above formula results in 1180, which makes sense based on how it works, so it just isn't what I am looking for.

As I said, it seems to me to really be a cross between the MAX() function and the GCD() function that I'm looking for but I can't wrap my brain out for the time being. Maybe I can try chaining GCD() functions instead of MAX() functions...

Thanks very much for assisting! I hope the explanation of my issue make sense.

Ack, totally missed your post, Marvin. That definitely does the trick. Is there possibly a way to condense all that into single formula using SUMPRODUCT() for the logical tests?

Hahahaa, fair enough. I'll tinker around with it a bit. My main concern is that this is something I will use in the future for varying value ranges and table sizes and setting up a large helper table just seems less flexible to me but, you're right, it gets the job done.