Hello excel guru's,
I am not sure how to go about this.
I have a sheet with some columns that i need to compare
some values to seee if they are in a lookup in the same
book, then return the id, but i also need a IF statement
to look at column B, to check if it's value is "SUP"

D E F K
2 DEV SVCPK SOME TEXT BLANK
3 SUP 20565 TBAVA BANK BLANK
4 MOD ADBOH SOME TEXT BLANK


Lookup table
A B
1 ADBOH A bank name
2 TBAVA A banks name
etc...

I want to fill in the blanks. I need to check 1st
if the value in column C matches anyvalue in the
lookup list, if it does return the id in the lookup
table to G else enter in "TEST", then i want to
lookup again because if the value in B says
"SUP" then it needs to look up the value from
D, but only the first 5, LEFT(D,5). From there
check against the lookup table again. If it matches
then return the id, else return "TEST".

I have one going already, but it only works with the
lookup for column C, it works, but i don't understand
how to get the IF statement in there, looking for
Column B's value.

Here is the actual code that works, but it doesn't
check for what i need and that is:
If column B.value = "SUP" then
loop through all the rows with SUP value
End if

Becasue i never will know how many rows, how
can i use the For i = 2 To LastRow, to put this in a loop.
Or do i even need to???
thanks for any help, it will be much appreciated.

Keri

code that works so far...
Sub fillBankID()

Dim LastRow As Long
Dim myrange As Range
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Output").Activate
Range("K2:L2").Select
Selection.Clear
Range("K2").Select
Range("K2").Formula = _
"=IF(ISNA(VLOOKUP(E2,CLientList,1,FALSE)),""NAME"" ,
VLOOKUP(E2,ClientList,1,FALSE))"
Range("L2").Select
Range("L2").Formula = "=VLOOKUP(K2,ClientList,2,True)"


With Worksheets("Output")
Range("K2").AutoFill Destination:=.Range("K2:K" & LastRow) _
, Type:=xlFillDefault
.Range("L2").AutoFill Destination:=.Range("L2:L" & LastRow) _
, Type:=xlFillDefault
End With

End Sub