Find Minimum Value and corresponding value from multiple tables - Index/Match maybe?

[edthedrummer]

Hello everybody,

I'm having a little difficulty trying to carry out the following task:

I have a number of tables, potentially up to six that contain data values exported from a third party software. Each table contains the same data channel headings set out in Column A, but differing values to those channels as the data is recorded.

When some/all of the tables are populated, I wish to look up the minimum value of one row in all tables, and then display a cell corresponding to that in the column below.

I have attached a file containing a dummy layout and potentially some further information to explain what i am trying to achieve.

I am open to different techniques.

Thanks in advance.

[Limor_OP]

Hi,
can you add manually to your file the result/s you expect to see in the cell you would like them be?
I think it would be easier to understand your intent, and help you with a solution.

Thanks.

[edthedrummer]

Thanks for your response. I have now uploaded Version 2, hopefully explaining what i am trying to do a little clearer.

[Limor_OP]

Hi,

Well, I found something that is a little clumsy , I created few helpers to overcome the complexity, I didnt find how to apply the result in one stage.

so first of all Ii found the smallest number in Heading B across all table content, and the value is in F1
Then I added a helper column & row in L & 4 respectively to search the smallest value horizontally and vertically.

Then - in I2 and J2 I fount the position of this value
and Finally - I brought in F2 the Heading D value corresponding to smallest Heading B Value.

This solution is valid only if same lowest value will not reccur twice in more than one Heading B title. In this case the formula will return the first result.


Hope this helps - and hope that maybe someone else will find a simpler formula to recive the desired result in one step.

[edthedrummer]

Hi,

Well, I found something that is a little clumsy , I created few helpers to overcome the complexity, I didnt find how to apply the result in one stage.

so first of all Ii found the smallest number in Heading B across all table content, and the value is in F1
Then I added a helper column & row in L & 4 respectively to search the smallest value horizontally and vertically.

Then - in I2 and J2 I fount the position of this value
and Finally - I brought in F2 the Heading D value corresponding to smallest Heading B Value.

This solution is valid only if same lowest value will not reccur twice in more than one Heading B title. In this case the formula will return the first result.


Hope this helps - and hope that maybe someone else will find a simpler formula to recive the desired result in one step.

Thankyou very much for that, it might not be completed in one step but that's much better than the alternative manual task! Really appreciate your time spent looking for solutions to that, well done!

I'm actually very impressed with how simple and effective the answer has become. I envisioned an extremely long equation, but breaking it down makes it much more understandable for me with basic excel knowledge.

Ed

[Limor_OP]

Hi,
I'm glad it helps you,meanwhile I found an error in my True/False formula in row 4 as it searches the whole column for the lowest number and not just in Heading B.
so if the lowest number is 1 it found the value in Heading A (B6) and returned the corresponding value in Heading D which is not what you are lookingfor.

So - here is the updated version.

Glad to help.

[edthedrummer]

Thanks again.

I've noticed in the formula you have used, you include a + symbol before a few formula's, for example +IF and +SMALL.

Is this something you add deliberately, or is this accidental? I can't understand what the +symbol implies for the formula.

Ed

[Limor_OP]

can you copy the formula you are referring to here, and mark bold the character/s you are asking about?

[edthedrummer]

=+OFFSET(B6,J2-4,I2-2)

=+SMALL(IF($A$6:$A$45="Heading B",$B$6:$K$45),1)

These are a couple of examples. I'm not sure if its just a formatting type of thing within excel after you have saved the document?

[Limor_OP]

oh, it's nothing essential, can be deleted with no effect on the result.

[edthedrummer]

Understood, many thanks for your help.

I've transferred the equations across and they are working perfectly. Great help, thankyou!

[Limor_OP]

Great, the only problem left to solve (in my opinion) is when you have multiple identical values which are identified as the smallest.

If, for example, total index of Heading B's lowest value is 12 but it appears both in table 1 and table 2 - the formula will return the 1st value it finds, i.e., table 1' value of Heading D. Is it OK with you?

[edthedrummer]

This shouldn't be a massive problem, the actual value that will be used as "Heading B" will be a time, in mm.ss.000 format with each value being different, so the chances of two times being recorded of the same value are extremely small.

[Limor_OP]

OK fantastic, anyway I added also the lowest cell postion in H2, for cases of duplicate values so you know where excel retrieved the result from, and to make it easier verify the correctness of the answer.