Simplified Example:
Lotto:
Multiple players have played on a number. Their played Numbers are listed in a table with two columns: Column 1 = Player, Column 2: Number which is played.
A winning number is found
Now I want to make a function that can find the player who has won
I have made a really long IF function. Could it be made simpler and / or not so long?
Here is my IF-function: =IF(E2=B2;A2;(IF(E2=B3;A3;(IF(E2=B4;A4;(IF(E2=B5;A5;(IF(E2=B6;A6;(IF(E2=B7;A7;"No match")))))))))))
If not is assured that there can be at most one winner any solution by MATCH / LOOKUP … can’t work. A deeply nested IF… anyway isn’t reasonable. Talking of a “LOTTO” I would assuime the participants choose their numbers themselves, and there can’t be the mentioned assertion.
You don’t need to look for a single (the first in a defined direction) match, but to collect all (zero or more) winners.
See attached example.
disask70753_LottoWinners.ods (13.9 KB)
On your screenshot E3 is blank. I guess you mean E2
=INDEX($A$2:$A$7 ; MATCH($E$2 ; $B$2:$B$7 ; 0))Sorry for the late respons - I have to find out everything about INDEX and MATCH
Thanks for your solution - The exact solution, I guess should be = IFERROR(INDEX($A$2:$A$7 ; MATCH($E$2 ; $B$2:$B$7 ; 0)); “no match”)
(Edit: function name - (Im using a Danish version and the help file is misleading)
Why not using LOOKUP?
=LOOKUP($E$2; $B$2:$B$7; $A$2:$A$7)
Because you use a spreadsheet as a database whereas LOOKUP is a pure spreadsheet function. It works differently than most people expect.
So in other words
If I wanted the the exact winning number - and if it could not be found, then the winning number that came closest, then I could use the LOOKUP? (EDIT: The database should be sorted in an ascending or descending order?)
No I’m wrong - I will not always get the closest winnernumber
Instead of finding “closest matching X” on the scale it is rather a “smaller or equal X” on the scale.
Integer points of school test with school grades. More points is better, “A” is the highest grade. We use some irregular scale of points in ascending order:
00 F [ 00 - 24 ]
25 E [ 25 - 47 ]
48 D
66 C
79 B
90 A [ 90 or higher]
Now we can LOOKUP, VLOOKUP or INDEX(…;MATCH(…)) in normal spreadsheet mode. Most teachers will play with the scale to get some “smooth” distribution.
The only number that would return #N/A error, would be a negative number.
The first spreadsheet program “Visicalc” was developed 1979 by employees of an insurance company where scales like this play an important role. It had the same LOOKUP function right from the start. Using spreadsheets as database surrogates was unimaginable back then.
You want to get the letter in A at the smallest number in B?
=INDEX($A$2:$A$7 ; MATCH(MIN($B$2:$B7) ; $B$2:$B$7 ; 0))Dont quite understand you, but no - I want to find the number in E3 - which is pointed to “player” E