-1

I am trying to match specific name (the names may be repeated several times) and to look up the name which has the lowest value in column D Here's a snapshot to illustrate the issue

enter image description here

I would like for example to lookup the name Name1 and return the one who has the lowest value. In this case I need to return the row number 4 as this one has only 12 in value I tried such a formula but didn't work for me

=MATCH("Name2",$A$2:$A$9,MATCH(MIN($D$2:$D$9),$D$2:$D$9,0))
JvdV
  • 70,606
  • 8
  • 39
  • 70
YasserKhalil
  • 9,138
  • 7
  • 36
  • 95
  • Did you mean to tag `VBA` instead of `Excel-formula`? Also, do you mean to get the minimum in general ***or*** the minimum for when you looking for `Name1`? It's a little unclear to me. – JvdV Sep 16 '20 at 08:57
  • @JvdV I mean minimum for the name I am looking for and either a formula or a code, – YasserKhalil Sep 16 '20 at 09:23
  • Alright, so now do you actually need the ***row*** number *or* the actual minimum, this example being `12`? – JvdV Sep 16 '20 at 09:26
  • To return the row number. – YasserKhalil Sep 16 '20 at 09:39

2 Answers2

2

You may use:

=MATCH(1,(A1:A9="Name1")*(D1:D9=MIN(IF(A1:A9="Name1",D1:D9))),0)

Note: It's an array entered formula using CtrlShiftEnter.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Amazing. Thank you very much. What if I need to add another criteria .. I mean to lookup by first name and last name? I can't test now but I think `(B1:B9="Lname1")` should be added. Am I right? – YasserKhalil Sep 16 '20 at 09:45
  • 1
    @YasserKhalil, in that case you could extend the boolean logic in the first part of the formula ***and*** use a nested `IF` statement in the first `TRUE` parameter, like so: `=MATCH(1,(A1:A9="Name1")*(B1:B9="LName1")*(D1:D9=MIN(IF(A1:A9="Name1",IF(B1:B9="LName1",D1:D9)))),0)` – JvdV Sep 16 '20 at 09:52
1

If you have Office 365, you can use the new XMatch function with an array returned by an IF statement. Like this formula, which assumes that the text "Name1" is in cell F1.

=XMATCH(0,IF(A1:A9=F1,D1:D9,NA()),1,1)

enter image description here

edit after comment: If you can't use Xmatch, then the data needs to be sorted by column D values from large to small. Then you can use Match() with a -1 as the last argument to look up the next value larger than 0. XMatch can do that with unsorted data, but Match needs sorted data for that.

Also, if you don't use Office 365, the formula needs to be confirmed with Ctrl + Shift + Enter, because it is an array formula. If you enter it in Office 365 Excel, that's not needed, but if people edit the workbook with an older version of Excel and edit the formula, they need to use Ctrl + Shift + Enter.

teylyn
  • 34,374
  • 4
  • 53
  • 73