Thanks:  0
Likes:  0

1. I have a worksheet with validated cells one is products the other is prices. Each list has several entries. What I want to do is tie them together so that when I select a product the correct price also comes up.

thanks.

G.

2. Use VLOOKUP to display the price based on the product choice.

3. I'm not familiar with vlookup. I looked at it and couldn't figure it out. Could you explain a bit more.

my sheet is as follows

cells b2 c2 d2
Products blank prices

thanks again.

G.

4. Create a table somwhere on your sheet with two columns, One for product, one for the price. Say it's in \$P\$3:\$Q\$12. Now, if your product is in B2 and you want the price in D2, place this formula in D2

=VLOOKUP(B2,\$P\$3:\$Q\$12,2,FALSE)

This will return the price next to the product from your table.

See help file for examples

5. For the previous example.

=VLOOKUP(B2,\$P\$3:\$Q\$12,2,FALSE)

\$P\$3:\$Q\$12 must be sorted by column P, and take note that column P is where the formula looks up B2.

6. Hey Cosmos,

that last FALSE arguement forces VLOOKUP to find an exact match, if it doesn't find it, it returns #N/A : this means you don't need to sort your VLOOKUP range at all - as an exact match only will do

you only need to sort the range if you don't use FALSE as the last arguement (ie TRUE, 1 or ommitted). In these cases, if it can't find an exact match, it will bring back the closest (hence the need for sorting) as it just defaults to the next value

you can also use 1 and 0 instead of TRUE and FALSE if you want to save time and space.

7. Chris Davison,

Thanks for the correction, I totally forgot about that! Am glad somewhat caught that!!

8. ## Re: validation lists

Using the vlookup, how can I code in VBA code?

CostCenter = Application.VLookup(Range("RangeName").Value, _
Worksheets("sheet2").Range("Rlookup"), 2, False)
If Not IsError(CostCenter) Then
cell.Offset(0, 1).Value = CostCenter
End If

wage = Application.VLookup(Range("RangeName").Value, _
Worksheets("Sheet2").Range("Rlookup"), 3, False)
If Not IsError(wage) Then
cell.Offset(0, 1).Value = wage
End If

Above VBA code I had define the range for "RangeNAme","Rlookup"... Any mistake in the code? I can't execute it... It gave me error message! Any help is very appreciated..

9. Originally Posted by Cosmos75
Chris Davison,

Thanks for the correction, I totally forgot about that! Am glad somewhat caught that!!
With "must" you were in error. But, if the table can be sorted, the speed of linear search with VLOOKUP(...,FALSE) will improve.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•