Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching or lookup functions
I have 2 columns of data (A and B) both with unique alphanumeric codes in
each cell (unique to each column but not between columns; ie some values are found in both columns...these are what I need to find and mark). I would like to know whenever a value in column B is also found in column A. If the value in a given row in column A is found anywhere in column B, I would like to return a 1 in the same row of column C (as where the value is in column A). Otherwise, I want to return a blank. Can anyone assist? I thought this might be doable with vlookup but I could not figure out how to look for multiple #s in vlookup function...it seems to only look for one value. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching or lookup functions
Try this:
=IF(COUNTIF(B$1:B$100,A1),1,"") Adjust the range to suit. Copy down as needed. -- Biff Microsoft Excel MVP "anand" wrote in message ... I have 2 columns of data (A and B) both with unique alphanumeric codes in each cell (unique to each column but not between columns; ie some values are found in both columns...these are what I need to find and mark). I would like to know whenever a value in column B is also found in column A. If the value in a given row in column A is found anywhere in column B, I would like to return a 1 in the same row of column C (as where the value is in column A). Otherwise, I want to return a blank. Can anyone assist? I thought this might be doable with vlookup but I could not figure out how to look for multiple #s in vlookup function...it seems to only look for one value. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching or lookup functions
worked like a charm, thanks. I was overthinking it.
anand "T. Valko" wrote: Try this: =IF(COUNTIF(B$1:B$100,A1),1,"") Adjust the range to suit. Copy down as needed. -- Biff Microsoft Excel MVP "anand" wrote in message ... I have 2 columns of data (A and B) both with unique alphanumeric codes in each cell (unique to each column but not between columns; ie some values are found in both columns...these are what I need to find and mark). I would like to know whenever a value in column B is also found in column A. If the value in a given row in column A is found anywhere in column B, I would like to return a 1 in the same row of column C (as where the value is in column A). Otherwise, I want to return a blank. Can anyone assist? I thought this might be doable with vlookup but I could not figure out how to look for multiple #s in vlookup function...it seems to only look for one value. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching or lookup functions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "anand" wrote in message ... worked like a charm, thanks. I was overthinking it. anand "T. Valko" wrote: Try this: =IF(COUNTIF(B$1:B$100,A1),1,"") Adjust the range to suit. Copy down as needed. -- Biff Microsoft Excel MVP "anand" wrote in message ... I have 2 columns of data (A and B) both with unique alphanumeric codes in each cell (unique to each column but not between columns; ie some values are found in both columns...these are what I need to find and mark). I would like to know whenever a value in column B is also found in column A. If the value in a given row in column A is found anywhere in column B, I would like to return a 1 in the same row of column C (as where the value is in column A). Otherwise, I want to return a blank. Can anyone assist? I thought this might be doable with vlookup but I could not figure out how to look for multiple #s in vlookup function...it seems to only look for one value. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup the next matching record. | Excel Worksheet Functions | |||
matching and lookup?? | Excel Worksheet Functions | |||
matching and lookup?? | Excel Worksheet Functions | |||
Matching unsorted lookup values | Excel Worksheet Functions | |||
Index & Matching Functions | Excel Discussion (Misc queries) |