Excel Price Lookup: VLOOKUP or INDEX

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:
PromoPrice01

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))

PromoPrice02

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)
PromoPrice03
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)

PromoPrice04

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?
________________