Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing columns
how do i compare a Cell A1 in Worksheet 1 to the whole Column B in Worksheet
2 to see if part of any of the contents in Cell A are anywhere in the list in Column B. i.e. Cell A1 for Worksheet 1 = hlj104 and the list of values in worksheet 2 Column B read: B1: ID=swk149 B2: ID=ldj322 B3: ID=ism572 B4: ID=hlj104 B5: ID=wkl294 i need it to pick up and locate hlj104, now i've tried the =OR(EXACT(A1,'Worksheet2'!$B$1:$B:$5)) but its not exact cause the ID= is in front of the userid on worksheet2. so how can i find that on id,in a couple thousand rows on the other worksheet. thanks in advance... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comparing columns
If you just want to find it, this should work (given the data that you
provided) =VLOOKUP("ID="&B13,Sheet3!B13:B17,1,FALSE) with b13 containing the string you were looking for. Note, this just finds the string so you know it exists (unless it returns #N/A) "~Q" wrote: how do i compare a Cell A1 in Worksheet 1 to the whole Column B in Worksheet 2 to see if part of any of the contents in Cell A are anywhere in the list in Column B. i.e. Cell A1 for Worksheet 1 = hlj104 and the list of values in worksheet 2 Column B read: B1: ID=swk149 B2: ID=ldj322 B3: ID=ism572 B4: ID=hlj104 B5: ID=wkl294 i need it to pick up and locate hlj104, now i've tried the =OR(EXACT(A1,'Worksheet2'!$B$1:$B:$5)) but its not exact cause the ID= is in front of the userid on worksheet2. so how can i find that on id,in a couple thousand rows on the other worksheet. thanks in advance... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing columns | Excel Discussion (Misc queries) | |||
Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Need help with lookup and comparing values | Excel Worksheet Functions | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |