[Solved] Low Score Lookup

[Wutpa]

Hi All.

I have created spreadsheet for a season of competitions, with a different tournament on each page. The individual competitions have four rounds and I am trying to track and record the lowest score across all four rounds and the name of the player that scored it.

I have attached a simplified example of what I mean, with the scores from the four rounds in columns A-D and the player name in column E. I used the MIN function to find the lowest score of 55, recorded in cell H3, but what I really need is a formula to record the name of the player that got the lowest score, to be recorded in H4. I had been trying to use VLOOKUP but of course this fails, presumably because my datatable covers four columns which then makes my columnindex of 5 non-viable for any score outside of column A. So...VLOOKUP won't help me here, I get that.

Is there any formula or function that can produce the answer I'm looking for? In this example the desired result to be returned in cell H4 is D.

Many thanks for any answer/insight you can provide....even if that answer is a basic no!

Nigel.

[MrProgrammer]

First you need to say what should happen if the low score is not unique. What if A8 also contains 55?

[Zizi64]

The structure of the data is not appropriate for the function VLOOKUP(), therefore you need helper formulas for the parts of the data (works when the minimum value is unique):

Low Score Lookup.ods

(18.84 KiB) Downloaded 73 times

[Wutpa]

First you need to say what should happen if the low score is not unique. What if A8 also contains 55?

Fair question, my omission. For my purposes only the first instance of the lowest score needs to be recorded by name.

Thanks.

[Wutpa]

The structure of the data is not appropriate for the function VLOOKUP(), therefore you need helper formulas for the parts of the data (works when the minimum value is unique):

Low Score Lookup.ods

Thanks for this Zizi64. I figured additional data/formulas would have to be incorporated to make it work but I have neither the experience nor the brain power to come up with it on my own. Your solution looks like it might work though, so I'll test it out tonight and mark this thread as SOLVED if it's all good.

Thanks again.

[Lupp]

(2D-matching comes with a lot of complications.)

You may have a look into the attached demo.

[MrProgrammer]

For my purposes only the first instance of the lowest score needs to be recorded by name.

In a two dimensional array the term "first" is ambiguous: Orders on the Cartesian product of totally ordered sets Are rows or columns considered first? Same direction each time or Boustrophedon? Perhaps we should scan diagonally to find the item nearest to top left?

The simplest method would be to perform four VLOOKUPs, one for each column, then choose the "first" one that matches. Q2/A2 in the following tutorial explains how to deal with #N/A when using this function.

[Tutorial] VLOOKUP questions and answers

Or put =SUMPRODUCT(A2:D2<>$H$3)=4 in ZZ2 and fill the formula down to ZZ11. Then put =INDEX(E2:E11;MATCH(0;ZZ2:ZZ11;0)) in H4.

Or type =(A2:A11<>H3)*(B2:B11<>H3)*(C2:C11<>H3)*(D2:D11<>H3) in ZZ2 and press ⇪⌘Enter (Shift+Command+Enter) on a Mac or Ctrl+Shift+Enter on other platforms; if you press Enter by mistake, use Edit → Undo and try again. This should fill Z2:Z11. Then put =INDEX(E2:E11;MATCH(0;ZZ2:ZZ11;0)) in H4.

All these work whether the minimum is unique or not. They pick the name with the lowest matching row number.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Wutpa]

Thanks very much to Lupp and Mr Programmer for the suggestions posted, and to everyone who responded to my query.

I used the formula suggested by Mr. Programmer and that works well for my purposes and that got me out of the corner I'd panted myself in to, so thanks again.