Combine Values for Excel VLOOKUP

Combine Values for Excel VLOOKUP

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. Here's how you can combine values for Excel VLOOKUP formulas.

Here's what Marsha needs to do:

  • 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

The Data Layout

I don't have a copy of Marsha's Excel file, but her lookup table, with Parts information, might look something like the screen shot below.

vlookupAB01

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.

Enter an Order

When customers enter 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.

vlookupAB02

Single VLOOKUP Value

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:

=VLOOKUP($A3,PriceLU,3,FALSE)

Combine the VLOOKUP Values

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, from column 3 in the lookup table:

=VLOOKUP($A2 & IF($B2,"-","") & $B2,PriceLU,3,FALSE)

vlookupAB03

The same VLOOKUP formula is used in column C, with the lookup column changed to 2, to get the Part description from 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!
__________

0 thoughts on “Combine Values for Excel VLOOKUP”

  1. Hi,

    I am trying to design a inventory management template as shown in the video, but the problem is that I don't know how to update/delete any value which has been added using the form.
    I also want to read data from excel sheet using the forms, e.g. a form should appear saying that please enter your part number , and once I enter the part number then some detail should appear on the screen.

    Thanks in advance.

  2. I have a table with 3 coulmns, Level, User Name, and Password. I also have a Start Page with form fields for User Name and password.
    I have the user input their name and assigned passwrod. The vlookup does this great and provides with the Level. What I need is to hide 3 worksheets and display a certain one based on the Level result.
    Any idea?
    Thanks

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.