Help with looking up value in list

[FurRelKT]

Hello,
I need some help understanding what i need to do inorder to :
1. take the value in Q2, it is a string
2. compare it to a value in the list in another sheet
3. look at the first column in the lookup, then it might not match, so
look in the second column.
(my lookup has three column, the first is the main column, an alias in
the second column, then a type column.)

for ease this example is using row 2 insead of i:
Sheet1:
L2 Q2
[need the formula here] TESTA
[need the formula here] THIS

lookup product list
A B C
1. TEST TESTA Primary
2. THIS THISA Primary

What i need:
In the first row, Q2, [TESTA] the value i need checked, is checked in
the lookup for a match, it doesn't find it in the first column, so it
goes to the next column in the lookup to check it, it's found in the
second column in the lookup so, i want to fill the value in L2 with
that value from the lookup [TESTA]. If it still doesnt find the match,
then put "Bad" in L2.
In the second row, didn't need to go to the second lookup column
because it was already found in the first column of the lookup.

this is what i have tried:
**already in a loop
strRel = Cells(i, 17).Value
Cells(i, 12).Formula = _
"=IF(ISERROR(VLOOKUP(" & Cells(i, 17).Address & _
",ProductList,1,False)),""BAD"",VLOOKUP(" & _
Cells(i, 17).Address & ",ProductList,1,False))"

Now, i dont know how to check for the other column in the
lookup...please help.

Maybe i should be doing this another way than lookup.... any ideas will
really be helpful as well as explaining the way it's done.

Hope that i have explained what i need,Thanks in advanced.

Keri

 

[Guest]

Maby this way:


Sub xFind()

Dim w, s, r, rw

s = Range("Q2").Value
rw = Sheets("ProductList").Cells(65500, 1).End(xlUp).Row
Set w = Sheets("ProductList")
For Each r In w.Range("A1:B" & rw)
If r.Value = s Then
Range("L2") = r.Offset(0, 1)
Exit Sub
End If
Next
Range("L2") = "Bad"
End Sub



"FurRelKT" skrev:

 

[FurRelKT]

Maby this way:


Sub xFind()

Dim w, s, r, rw

s = Range("Q2").Value
rw = Sheets("ProductList").Cells(65500, 1).End(xlUp).Row
Set w = Sheets("ProductList")
For Each r In w.Range("A1:B" & rw)
If r.Value = s Then
Range("L2") = r.Offset(0, 1)
Exit Sub
End If
Next
Range("L2") = "Bad"
End Sub

Thank you so much excelent, I will give this a try.

K~

 

[FurRelKT]

excelent, this is how i finished it,

Case "REL"
If cells(i, 11).Value = "" Then 'if cell has blank
bankid
cells(i, 16) = cells(i, 5)
mycode = cells(i, 5).Value
cells(i, 11).Formula = _
"=IF(ISERROR(VLOOKUP(" & cells(i, 16).Address & _
",
ClientList,1,False)),""Fiserv"",VLOOKUP(" & _
cells(i, 16).Address &
",ClientList,1,False))"
cells(i, 17) = cells(i, 2)

Dim w, s, r, rw
s = cells(i, 17).Value
rw = Sheets("Projects").cells(65500,
1).End(xlUp).Row
Set w = Sheets("Projects")
For Each r In w.Range("A2:B" & rw)
If r.Value = s Then
cells(i, 12).Value = r.Value
cells(i, 13).Value = r.Value
Exit For
End If
cells(i, 12) = "Bad"
Next r
End If

Thank you so much for your help.

Keri

 

[FurRelKT]

I thought this took care of the issue, but it does not. for some
reason, it is skipping ones that are there in the list. I should not
say 'skip' but not catching the match....

i need to work on this further.

any help would be appreciated.

K