INDEX MATCH with MIN or SMALL or similar

[whahnelt]

SAMPLE FILE NOW ATTACHED IN THREAD.

Hi,

I need to track values on a daily basis and extract the lowest value from a table based on two criteria (row and column). Scenario as simplified below - sorry for this, my firewall does not let me attach files and I would need help before going home where I could use my private computer :-(

Channel ChA ChB
Prod1 Day1 500 600
Prod1 Day2 500 580
Prod2 Day1 400 500
Prod2 Day2 380 510

I need to find the lowest value per model and channel, desired output below:

Channel ChA ChB
Prod1 500 580
Prod2 380 500

Hope this makes sense...

I have tried INDEX MATCH with MIN, with SMALL and even with AGGREGATE but I can't get it to work...

Thanks for any tips!!
Wiebke

[Pepe Le Mokko]

Please read the yellow banner....

[whahnelt]

Please read the yellow banner....

I am aware and I apologize! Unfortunately, I cannot upload files from work due to annoyingly fierce firewall but would have needed help urgently :-(
Sorry...

[Pepe Le Mokko]

And can you open a file when one is attached in a post?

[whahnelt]

And can you open a file when one is attached in a post?

Yes, that works.

[Pepe Le Mokko]

Does the attached help?

[whahnelt]

Does the attached help?

First of all big thanks for helping me anyway!!!

Regarding your suggestion I also need to match the column to the channel name (as there are many in the raw data and I cannot define fixed column). I was trying to adapt your formula as follows (for cell F8):

Please Login or Register  to view this content.

But it's not working, returns "0" :-/

I am also home now and could prepare a sample file but yours is representative of teh problem so I'm not sure if that would help...

[whahnelt]

Does the attached help?

Sorry, I of course intended to adjust the formula as follows (again for F8):

Please Login or Register  to view this content.

But now it returns "1"...

[whahnelt]

Now I created a sample file and can upload from home, please find attached.

[FlameRetired]

Try in C5:F6. It returns MIN like the upload indicates.

Please Login or Register  to view this content.

For MAX change (15,6, to (14,6,.

[whahnelt]

Try in C5:F6. It returns MIN like the upload indicates.

Please Login or Register  to view this content.

For MAX change (15,6, to (14,6,.

Works great in the sample file. First quick trial not yet working in my original data (returning NUM error) but I will give it some more thought later and figure it out I hope!! :-)
Thanks already and I will get back if anything...

[whahnelt]

Try in C5:F6. It returns MIN like the upload indicates.

Please Login or Register  to view this content.

For MAX change (15,6, to (14,6,.

Got it - thanks a million!!
(Of course my data export is delivered stored as text but VALUE helped with that).

THANKS AGAIN!!!

[FlameRetired]

You are welcome. Thank you for the feedback and marking your thread Solved.