multiple matches?

[stadleybear]

Hi, there, hoping someone can help a.s.a.p this is for an imminent project hand in for uni!.
In column A in the table below there are three lowest values (16), how do I compare the values in column C that correspond to the three lowest values in column A, find the lowest value, and then return values in that row?, i.e lowest value in C for lowest values in A=4053, return value in B for this row, 8480 (or any column in this row).

A B C
36 3816 4382
24 3984 4619
24 4320 6352
16 4256 4647
24 11040 5536
16 8480 4053
16 9600 4411
24 33120 6080

Thanks in advance

[Lemice]

Hello,

Assuming you just want to find one value basing on that, you can paste this Array formula in D1

Please Login or Register  to view this content.

When you enter it, you will need to hold Ctrl-Shift, then hit enter, because it is an Array Formula.

Hope this help.

Edit: Here is a sample file basing on your example.
Find smallest value.xlsx
Next time please prepare a sample on your own and attach it before posting, making it easier for others to help you.

[Harribone]

You need to find rows which are the lowest value, I've done this in Col D. If that row has the lowest value in it results with Col C. Col D then finds the lowest value from Col C and returns a 1.

This is then used on the right as part of the index/match to show the row result as needed.

[aydeegee]

Hi StadleyBear

Welcome to the forum.

Is it cheating to use a helper column? At the back of my mind I am thinking that there may be a way using an array - but it's beyond me.

I have managed to get this to work - hope this helps

Regards
Alastair

(edit) See I told you there should be an array solution!

[stadleybear]

Thanks all, Lemice takes the prize with the array formula!, not sure i understand it but i will try and de-construct it later.

[Lemice]

Glad I can help.

For the Array formula,

Please Login or Register  to view this content.

I look up the value using INDEX MATCH combo, with SMALL serves as the function to return value that match your criteria.
The first core of the formula is SMALL(A2:A9,1), returning the smallest value in Column A, and in this case, 16
The second core of the formula is SMALL(IF(A2:A9=SMALL(A2:A9,1),C2:C9),1), which mean, "Look up smallest value in Column A, then return smallest value among the list of value found accordingly in Column C " (In this case, smallest among {4647,4053,4411} because their value in A, same row is 16, thus is 4053)
After that, I do a simple MATCH to look up a "text" when combine 2 smallest value (combine 4053 and 16 as text, we have "405316") among a list of combined texts by combine Column A and Column C. This will pinpoint the exact row which match both of the criteria.
I'm sorry if this sounds complicated, because I am not really good with explanation, but that is the basic idea, and let me know if you have any further question