Lookup Data in Sheet1 on Sheet2 or Sheet3 and Return Value

[helpexcel]

Hi - How do I lookup an item on 2 lists with out the ws3 overriding the ws2 inputs? basically changing it so that if it cant find the item from ws1 in ws2, then look in ws3. This is the code I tried to modify to add ws3. thanks!

[COLOR=#333333]With CreateObject("scripting.dictionary") [/COLOR]
[COLOR=#333333].CompareMode = 1[/COLOR]
[COLOR=#333333]For Each Cl In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333].Item(Cl.Value) = Cl.Offset(, 2).Value[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]Cl.Offset(, 1).Value = .Item(Cl.Value)[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333].Item(Cl.Value) = Cl.Offset(, 2).Value[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]Cl.Offset(, 1).Value = .Item(Cl.Value)[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]End With[/COLOR]

 

[Fluff]

Try it like

With CreateObject("scripting.dictionary")
   .CompareMode = 1
   For Each Cl In Ws2.Range("E45", Ws2.Range("E" & Rows.Count).End(xlUp))
      .Item(Cl.Value) = Cl.Offset(, 2).Value
   Next Cl
   For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
      If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
   Next Cl
   For Each Cl In Ws1.Range("A11", Ws1.Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 1).Value = .Item(Cl.Value)
   Next Cl
End With

 

[helpexcel]

Thanks!
I tried stacking this code for multiple items, and it put this - Add Cl.Value, Cl.Offset(, 2).Value - in all of the columns with a value from ws3.

With CreateObject("scripting.dictionary")      
.CompareMode = 1


        For Each Cl In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 2).Value
        Next Cl
        For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
        If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
        Next Cl
        For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
        Next Cl


        For Each C1 In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))
         .Item(C1.Value) = C1.Offset(, 15).Value
        Next C1
        For Each C1 In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
        If Not .Exists(C1.Value) Then .Add C1.Value, C1.Offset(, 15).Value
        Next C1
        For Each C1 In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         C1.Offset(, 12).Value = .Item(C1.Value)
        Next C1

 

[Fluff]

Did the first part work, before you added the next code?
What are you trying to do with the part that you added?

 

[helpexcel]

The first part works great, and i can stack and it works great. it just gets hung up on the "not exists" part.
I'm just trying to fill in data based on the value in ws1 column A.

 

[Fluff]

Which "not exists" part & what do you mean by "gets hung up"?

 

[helpexcel]

The info will pull correctly from ws2. However, the info will only pull from the first code for ws3. So columns 2 and 13 will both have the data from ws3 column 3.

[COLOR=#333333] For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
[/COLOR][COLOR=#333333]        If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value[/COLOR]

 

[Fluff]

I'm afraid I simply don't understand what you are saying.
Also can you please answer my questions?

 

[helpexcel]

this part of the code works great.

For Each Cl In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))         
.Item(Cl.Value) = Cl.Offset(, 2).Value
Next Cl



For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
Cl.Offset(, 1).Value = .Item(Cl.Value)
Next Cl

this part does not

For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))        
If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
Next Cl
       
For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
Cl.Offset(, 1).Value = .Item(Cl.Value)
Next Cl

I have a list on ws1 and data in ws2 and ws3. I want to lookup column A on ws1 in ws2 and if it's not there look in ws3. The code works for the first column, where it will pull column 3 from ws2&ws3 and put it in column 2 on ws1. However if it then will take column 16 from ws2 and put it in column 12 on ws2, but it will take column 3 from ws3 and put it in column 16 on ws1. I have more lines where it will do the same thing. It will pull ws2 correctly for every line of code, but it will only pull column 3 for ws3.

 

[Fluff]

Lets start from the beginning, does this code do what you originally asked for?

With CreateObject("scripting.dictionary")
   .CompareMode = 1
   For Each Cl In Ws2.Range("E45", Ws2.Range("E" & Rows.Count).End(xlUp))
      .Item(Cl.Value) = Cl.Offset(, 2).Value
   Next Cl
   For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
      If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
   Next Cl
   For Each Cl In Ws1.Range("A11", Ws1.Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 1).Value = .Item(Cl.Value)
   Next Cl
End With