This week, Glen emailed me for advice on extracting prices from a lookup table. Some products have a promotional price each month, but other products are sold at the regular price.

I’m blocking email attachments these days, so I can’t show you the exact setup of Glen’s Excel worksheet. However, a simplified version might look something like this:

### Use VLOOKUP to Find Pricing

In his email, Glen mentioned that he is using a VLOOKUP formula.

- If there is a promotional price, he wants VLOOKUP to return the value from the Promo Price column.
- If there is no promotional price, Glen wants the price from the Regular Price column.

To do that, Glen could use the IF function, with VLOOKUP:

**=IF(VLOOKUP(F3,$B$3:$D$6,2,0)=0,
VLOOKUP(F3,$B$3:$D$6,3,0),
VLOOKUP(F3,$B$3:$D$6,2,0))**

### CHOOSE the Right Price

Another option is to use the MATCH function to find the row that the product is in. This formula is in cell H3:

**=MATCH(F3,$B$3:$B$6,0)**

Then, in cell G3, use the CHOOSE function and the INDEX function, to get the right price:

**=INDEX(CHOOSE((INDEX($C$3:$C$6,H3)>0)+1,
$D$3:$D$6,$C$3:$C$6),H3)**

### How the CHOOSE Formula Works

In this example, the CHOOSE function selects the correct pricing column to use for the prices. The **INDEX** function returns the price from the selected column.

First, the **INDEX** function returns the price from the promo column, for the selected product, and we check to see if the price is greater than zero:

**INDEX($C$3:$C$6,H3)>0**

- If there is NO promo price, the result is FALSE (0)
- If there is a promo price, the result is TRUE (1)

We add 1 to the result, so

- FALSE=1
- TRUE=2.

Next, the CHOOSE function returns a reference to the selected range.

- FALSE (1) = $D$3:$D$6
- TRUE (2) = $C$3:$C$6

Finally, the first **INDEX** function returns a price from the selected column, in the row for the selected product.

### How Would You Solve the Problem?

I’m sure there are several other ways to solve Glen’s lookup problem. What formula would you use?

________________