O'Reilly logo

Excel Hacks, 2nd Edition by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #105. Lookup from Within a Cell

Lookup from Within a Cell

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 Cat, type 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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required