Using vlookup without overwriting values that isnt in the lookup sheet

Occasional Visitor

Hi, im looking to use the vlookup function without overwriting the cells that isnt in the lookup table. 
My company have an excelsheet of all the item prices from our factories. When we are getting price increases from one of the factories I want to use vlookup to get it in our sheet without overwriting the other prices that isnt in the table/from the other factories. 

See my example attached.
I want to get the new prices in "Factory A New prices" to "Item price sheet". The rows with "Factory = B" should be unchanged. 

Appriciate any help!

2 Replies

@NorthernKarsten 

You cannot use a formula in column B for this - it would lead to circular references.

You can simply copy/paste the new prices, or if you prefer, run a macro:

Sub UpdatePrices()
    Dim r As Long
    Dim m As Long
    With Worksheets("Item price sheet")
        m = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 2 To m
            If .Range("C" & r).Value = "A" Then
                .Range("B" & r).Value = Application.VLookup(.Range("A" & r).Value, _
                    Worksheets("Factory A New Prices").Range("A:B"), 2, False)
            End If
        Next r
    End With
End Sub

@NorthernKarsten As a variant, if you are in to a solution that involves Power Query, (PQ) you could have your "overall" worksheet connect to all individual factory price lists. With the right steps within PQ, it achieve the same as VLOOKUP, but without the need to ever have to update formulae.

 

Factory A issues a new price list? Just upload it to the folder with the current price lists for all factories. Open your "overall" sheet and press Refresh All on the Data ribbon. That's it.

 

Something you would like to learn more about? Check out the site in the link below:

https://exceloffthegrid.com/power-query-introduction/ 

Chapter 9, focuses on importing files from a folder. You'll have to go through a learning curve, but I guarantee you that it's worth it.