Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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~ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
create new list from list A, but with exclusions from a list B | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions |