Excel-lent Trivia Quiz

Answer: HLOOKUP

If you are aware that VLOOKUP is derived from "Vertical Lookup" then in might not be such a leap to conclude that the second version is derived from "Horizontal Lookup". As implied in the question, this command works as VLOOKUP except for the looking along and down instead of down and along.
XLOOKUP is a command in some versions of Excel 365 which can replace both versions and extends the options to handle missing data. It also can be used to return an array, a range of values or just a single cell value, where the vertical and horizontal lookups only return the value of a single cell.

The other options don't (in January 2021) exist as commands in Excel.

Answer: OFFSET and MATCH

Now some would say that if you are in this situation it is the result of poor spreadsheet design, but sometimes you need to be able to use more than one index column in a table without duplicating data. Sometimes the data supplied from other sources just isn't laid out as nicely as one would like. For whatever reason, knowing how to overcome this is advantageous.
I won't cure you of insomnia with a worked example; suffice it to say that OFFSET, in the simple version, returns the value of a cell that is a given number of rows and columns away from a starting cell. By using the MATCH command on the column that is to be used, an index the number of rows to offset can be found, then the number of columns to the right (positive numbers) or left (negative numbers) as desired is entered as the last argument.

Other methods are available including the function XLOOKUP in Excel 365 but knowing how to combine OFFSET and MATCH is a useful technique.

The other options are all valid Excel functions but none of the listed combinations would help to resolve the problem.

Answer: 07-JAN-2021 10:43 (Incremented by one day)

To comprehend this, it is necessary to understand how Excel (and all other spreadsheets I have encountered) store dates. Dates are stored as an integer representing the number of days since an arbitrary start point. Usually, day 1 is the 1st January 1900 or 1st January 1904. Windows versions of Excel default to the former, those on Apple have traditionally used the latter. The principal advantage of the 1904 system is that a leap year occurs every four years for nearly two centuries whereas the former system in Excel fails to account for 1900 not being a leap year (apparently this is for compatibility with older spreadsheet programmes).

Time is stored as a fraction of the 24-hour day, eg 0.25 would represent 6am. Adding 1 to a date increases the integer part, the number of days since day 0. In the quoted example, the new date is the next day 07-JAN-2021. The numbers to the right of the decimal point are unchanged, thus the time element is unchanged.

Answer: INDIRECT

This might not seem very useful in itself, but as spreadsheets get bigger the usefulness of being able to treat (for instance) the names of other tabs in a workbook as a variable is seen.

One of the strengths of spreadsheets is using the same formula down a range of cells. Changing the formula for every cell to adapt to changes is wasteful. A formula that accounts for different situations is always to be preferred.

As an example, if I have a spreadsheet of my submitted FunTrivia Quick Questions, which includes tabs named Science, Literature and Geography, on each I have a calculation in cell A1 telling me how many questions I have submitted in that category.

Here's an attempt at a simple example. On my summary page I start with the subjects Science, Literature & Geography in A1-A3 respectively
I could write in B1 "=Science!A1", in B2 "=Literature!A1".
But a more consistent way would be to use the formula in B1: =Indirect(A1&"!$A$1"). Then copying this formula down to B2 & B3. This can also be extended if more categories are added - the list in column A can be sorted and the formulas will still work correctly.

Answer: CHOOSE

The possibly useful answer is CHOOSE. But this function requires the value on which it is acting to be an integer and it starts with what to do if the argument is a 1, then if it is a 2 and so on until the function is closed. The format is as follows
=CHOOSE({value},{what to do if value=1},{what to do if value=2},...)
This might not seem helpful especially as the argument on which you make your decision has to take values starting from 1. But VLOOKUP comes to the rescue. Since there only so many values the argument can take, using a table to map them on to the integers starting at 1 is the simplest way to go. Replace the {value} with the result of the VLOOKUP and problem solved. As always with Excel, nesting function within other functions can often overcome these initial drawbacks.

Answer: ROMAN

There is such a function and as an easy aide memoire it is called ROMAN.
This is one function I have never used in anger as nothing I have worked on requires an output in roman numerals.

Not surprisingly, it only works for positive values and rounds down to the nearest integer below, since only positive integers are representable in Roman numerals. Still the function is there for those who do need it.

The other listed functions are imaginary at the moment but there are functions such as OCT2BIN which converts octal to binary.

Answer: History

History is a reserved name for worksheets and is not allowed. It is used to track changes in the workbook. Why Microsoft thought this was a good idea and didn't use something like XJKUJIDWE which is less common for users to want I don't know. There are work arounds available, the simplest being to put a space or some other character at the end of the name. Spaces in names of tabs can be handled but the formatting of references is slightly different. Whichever you do care may need to be taken with the INDIRECT function to ensure desired output from your analysis.

(As far as I can tell this issue is common to all currently available versions of Excel up to and including January 2021).

Answer: LARGE

The command to use here is LARGE. The format is LARGE({array},k), this returns the nth largest number in the given array. So to obtain the second largest set k = 2. Using k = 1 will return the largest number, the same as MAX but more flexibility is derived from using LARGE. Likewise the command SMALL can be used to return the kth smallest number in a range of cells.

Answer: SUBSTITUTE

The function might not seem that useful at first glance but when large amounts of data are being used it can strip out unwanted text, standardise spellings and generally simplify text handling. At least that is the way I have used it.

The other options are also Excel functions but not ones I have ever found necessary to use.

Answer: CELL

All of these are Excel functions but CELL is the only one to return information about the cell as opposed to acting on the values of data.
The function CELL can also return information on the formatting and whether the cell is protected. These are useful for error trapping and handling when combined with other functions such as IF. Other uses exist but that is for the reader to pursue.