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?

________________

You may also like...

13 Responses

  1. Somnath says:

    If someone is already using the MATCH function to determine the row, the following would work too:

    =IF(INDEX($C$3:$C$6,$H$3)>0,INDEX($C$3:$C$6,$H$3),INDEX($D$3:$D$6,$H$3))

    With Regards,

  2. Rick Rothstein (MVP - Excel) says:

    This single formula in G3 (no need for a formula in H3) seems to work…

    =INDEX($C$3:$D$6,MATCH(F3,$B$3:$B$6,0),1+(INDEX($C$3:$C$6,MATCH(F3,$B$3:$B$6,0))=””))

  3. Rick Rothstein (MVP - Excel) says:

    I was just looking at using a calculated value in H3 to reduce the size of the formula I just posted and noticed that something looks wrong with the value you show for the formula you have in H3. You show it returning 3, but I get it returning 1 (because you have all ranges starting from Row 3)… to get it to return 3, you would have to start all your ranges at Row 1. Anyway, using the formula you show for H3 (so that it returns 1, not 3 in H3… just re-label it Index instead of Row), my formula for F3 would be this…

    =INDEX($C$3:$D$6,H3,1+(INDEX($C$3:$C$6,H3)=””))

    This formula only involves 2 function calls in F3’s formula as opposed to the 3 function calls your CHOOSE formula requires.

  4. Jeff Weir says:

    I’d use sumproduct alone: =SUMPRODUCT((F3=B3:B6)*((C3:C6=0)*D3:D6+C3:C6))

    No need to reference the ROW cell any more.

  5. Elias says:

    Maybe this,

    =INDEX(IF($C$3:$C$6>0,$C$3:$C$6,$D$3:$D$6),MATCH(B3,$B$3:$B$6,0))
    Ctrl+Shift+Enter

    Or

    =INDEX(INDEX(IF($C$3:$C$6>0,$C$3:$C$6,$D$3:$D$6),0),MATCH(B3,$B$3:$B$6,0))

    Or

    =INDEX(INDEX(($C$3:$C$6)+($D$3:$D$6)*($C$3:$C$6=0),0),MATCH(B3,$B$3:$B$6,0))

    Regards

  6. Ola.S says:

    One option
    =SUM((F3=B3:B6)*IF(C3:C6>0,C3:C6,D3:D6))
    //Ola

  7. @Rick, in the MATCH formula, the selected product is Pens, which is in Row 3 of the lookup range.

    In the first example, Paper was selected, which is in Row 1. Could that be causing the confusion?

  8. Thanks for all the formula suggestions! This gives Glen plenty of options for his workbook.

  9. Rick Rothstein (MVP - Excel) says:

    @Deb… Yep, that was the cause of my confusion… I have to learn to read more carefully. Thanks.

  10. Contextures Blog » Excel Function Friday: Track Driver Hours says:

    […] Excel Price Lookup: VLOOKUP or INDEX […]

  11. Luke Wisbey says:

    FWIW, another example illustrating use of CHOOSE & VLOOKUP would be:

    =LOOKUP(9.99E+307,CHOOSE({1,2},VLOOKUP($F$3,$B$3:$D$6,3,0),1/(1/VLOOKUP($F$3,$B$3:$C$6,2,0))))

    as with some of the above it is assumed 0 itself is never a valid price – if it is you would adjust accordingly.

    Using this approach 2 VLOOKUPs are always performed (never one nor three)

    In light of the above, were we using XL2007 we could shorten the above c/o IFERROR

    =IFERROR(1/(1/VLOOKUP($F$3,$B$3:$C$6,2,0)),VLOOKUP($F$3,$B$3:$D$6,3,0))

    A max of 2 VLOOKUPs will be performed – only 1 where necessary

    Of course if one were to reorder the columns such that Promo were listed last (rather than first) a basic LOOKUP would suffice:

    =LOOKUP(9.99E+307,INDEX(($C$3:$D$6&””)+0,MATCH($F$3,$B$3:$B$6,0),0))

    The above would simply establish the last number present within the appropriate row of prices.

  12. Jason Morin says:

    I like Jeff Weir’s approach and just combined it with what comes natural to me: INDEX and MATCH.

    =INDEX((C4:C6=””)*D4:D6+C4:C6,MATCH(F3,B4:B6,0))

  13. JB Shandrew says:

    Hi Rick,

    I have been trying to contact you by email. All of the email attempts have been returned.

    In different threads xxxxNoSpAmnewsaT*******DoT###

    The reason that I am trying so hard to contact you, is because I work for a DOT and want to automate the Random stationing by establishing sub lots. Now I do this with very basic formulas in Excel, which any accidental deletion will erase all of my formulas, and data assocated with the formulas. This is why I would like to have a VBA program to do this for me, then save the results in a file, with a different file name each time the number of ramdom density tests have been taken.

    I can be emailed at jbshandrew@comcast.net

Leave a Reply to Luke Wisbey Cancel reply

Your email address will not be published. Required fields are marked *