[RESOLVED] Simple Lookup Help

[Esham]

What is the simplest way to lookup a value using VBA in Excel? Here is what I am trying to do. For each cell in a column range I am looking at the value i.e. "05569". That value corresponds to another value in my list/table i.e. "Engine". How to I extract "Engine" and enter it into another cell? Thanks in advance for your help. I know this is an easy one but I cannot figure it out using VBA (Data Dictionary, Array, List, Function VLOOKUP, etc.)???

"05569" = "Engine"
"05579" = "Plant"

[Siddharth Rout]

If you can upload your workbook then I can give you the exact code...

[Esham]

I'm sorry but I cannot upload the workbook. You can write the code using range("A1:A5") and returning the lookup value in range("B1:B5").

A1 = 012
A2 = 013
A3 = 014
A4 = 015
A5 = 016

012 = Mon
013 = Tue
014 = Wed
015 = Thu
016 = Fri

[Siddharth Rout]

Ok lets say (See picture Attached)

A1 = 12
A2 = 13
A3 = 14
A4 = 015
A5 = 16

and

A9 = 12
A10 = 13
A11 = 14
A12 = 15
A13 = 16

And

B9 = Mon
B10 = Tue
B11 = Wed
B12 = Thu
B13 = Fri

So if you run this code, it will do what you want... I have commented the code so that it is easy to understand...

Sub LookupValues()
    'Put formula in Cell B1
    'R9C1:R13C2 is the Range A9-B13 as mentioned above
    'RC[-1] is the cell before B1 where we are picking up the value to check
    Range("B1").FormulaR1C1 = "=VLOOKUP(RC[-1],R9C1:R13C2,2,0)"
    'Autofill formula down... till last cell
    Range("B1").AutoFill Destination:=Range("B1:B5"), Type:=xlFillDefault
End Sub