MATCH, INDEX functions in Excel
The Excel lookup functions are used to create formulas to find the specific information you search in a table.
Have a look a nice example how to use the INDEX function to select randomnly values in a table.
MATCH
MATCH function gives you the rank in a list of items of the item you are looking for.
=MATCH(what you want to lookup, lookup column, parameter)
Parameter:
0: if you want an exact match (most used)
1: if you want the biggest value that is just lest or equal (list in ascending order)
-1: if you want the smalles value that is bigger or equal (list in descending order)
=Match("TV", List of items, 0)
INDEX
=INDEX(Range, row r, column c)
The INDEX function returns the value in row r and column c.
If you have only a list with 1 column, you can omit the column.
=INDEX(list of values, row r)
Here the 3rd winner is Carrey.
=INDEX(C69:C75, 3) gives you Carrey
Perfect LOOKUP function by COMBINING MATCH and INDEX.
By combining both functions you get a perfect search function without the limitations of VLookup or Hlookup function.
You can search in any column or row and in any order.
In our case
=INDEX(prize, MATCH(C78, winners, 0) )
=INDEX(list of values, MATCH(what you want to lookup, lookup column, 0))