VLOOKUP Vampires

image Do vampires prefer a specific blood type? Type A? Type B? Type AB? Are you positive? During the holidays, they might drink glögg, or Cosmopolitans!
Anyway, Marsha probably isn’t a vampire, but she wants to choose A or AB when doing a VLOOKUP.

  • If there is only a value in column A, use A for the VLOOKUP
  • If there is also a value in column B, use A & B for the VLOOKUP

Note: Remember to vote for the Excel functions that you’d like to learn more about during the 30 Excel Functions in 30 Days challenge, starting January 2nd.

The Data Layout

I don’t have a copy of Marsha’s Excel file, but her lookup table might look something like this. In column A, there’s a 3 letter code for each part, and some codes have a number at the end, to indicate length. The lookup table is named PriceLU, as you can see in the Name Box, at the top left of the screen shot.
When customers are entering an order, they put in a part code, and sometimes they enter a part length. In columns C and D, a VLOOKUP formula can pull the description and price from the PriceLU table.

Combine the VLOOKUP Values

For a normal VLOOKUP formula, you’d refer to a single cell as the lookup value. For example, the following formula would get the price for a wrench (WRN) from the price lookup table.:
However, if there is a length in column B on the Orders sheet, you need to include that as part of the lookup value. In the first argument of the VLOOKUP formula, you can check for a value in column B. If there’s something in that cell, show a hyphen and the value in column B.
IF($B2,”-“,””) & $B2
Here’s the revised VLOOKUP formula, that combines the values in columns A and B, when necessary, to get the price:
=VLOOKUP($A2 & IF($B2,”-“,””) & $B2,PriceLU,3,FALSE)
The same VLOOKUP formula is used in column C, with only the lookup column changed, to get the description in column 2 of the lookup table.
=VLOOKUP($A2 & IF($B2,”-“,””) & $B2,PriceLU,2,FALSE)

More Ways to Combine VLOOKUP Values

You can visit the VLOOKUP page on the Contextures website, to see more ways to Combine VLOOKUP Values. And keep an eye out for vampires!