Help with looking up value in list
 

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

Help with looking up value in list
 

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:

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

Help with looking up value in list
 

excelent wrote:
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~

Help with looking up value in list
 

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

Help with looking up value in list
 

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