Usually, in order to use one of Excel's lookup functions, you are required to lookup from within a table of cells in a worksheet. This hack shows how to perform a lookup on a small number of items, without leaving the cell.
Let's assume you have a changing value in A1 and want to return a result to B1 that varies based on the value in A1. For example, keeping it simple, say you have a validation list in cell A1 that a user can choose from any one of either Cat, Dog, Mouse, Horse or Rabbit. Based on their choice, we want to display a different result in B1.
First, set up a validation list. In cell G1, type in the word
Dog in G2,
Mouse in G3,
Horse in G4, and
Rabbit in G5. Then, click in cell A1 and select Data → Data Tools → Data Validation (pre-2007, Data → Validation). Select List from the Allow: box, highlight G1:G5 in the Source: box, and click OK.