Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Multiple Instances in a List
Hello...
I am having a problem with the VLOOKUP function. I may not be able to use it the way I am trying to, and hopefully someone can give me some direction. I have data in two main columns, A and B. In column A, I have a list of Functions, many of which are repeated several times down the column. In column B, I have a list of Teams, again of which many are repeated. Basically, many Teams can share any given Function, thus causing the values to be listed more than once. In column C, I have only the unique Function values listed, meaning that each one only appears once. I also have the Team names list in Row 1 across starting at column D1, and progressing through M1. What I am trying to do is to take the the first value in colun C, find it in column A, then look at the value in column B... If the value in column B is equal to the value in D1 lets say, place an 'X' in the cell. The problem I am running into is that my formula is stopping after it finds the first instance. So for example, I can get it to place an 'X' under the first Team name, as long as that Team name is the first one it finds. =IF(VLOOKUP(C4,A4:B12,2,FALSE)=D3,"Yes","No") What I need it to do is basically look at all instances the Function value occurs in column A, and match them up (put 'X's) to all of the corresponding Teams. I realize this was not written very well, but any help will be greatly appreciated!! Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Multiple Instances in a List
Try this entered in D2:
=IF(SUMPRODUCT(--($A$2:$A$100=$C2),--($B$2:$B$100=D$1)),"x","") Copy across then down as needed. Adjust for the correct end of range. -- Biff Microsoft Excel MVP wrote in message ... Hello... I am having a problem with the VLOOKUP function. I may not be able to use it the way I am trying to, and hopefully someone can give me some direction. I have data in two main columns, A and B. In column A, I have a list of Functions, many of which are repeated several times down the column. In column B, I have a list of Teams, again of which many are repeated. Basically, many Teams can share any given Function, thus causing the values to be listed more than once. In column C, I have only the unique Function values listed, meaning that each one only appears once. I also have the Team names list in Row 1 across starting at column D1, and progressing through M1. What I am trying to do is to take the the first value in colun C, find it in column A, then look at the value in column B... If the value in column B is equal to the value in D1 lets say, place an 'X' in the cell. The problem I am running into is that my formula is stopping after it finds the first instance. So for example, I can get it to place an 'X' under the first Team name, as long as that Team name is the first one it finds. =IF(VLOOKUP(C4,A4:B12,2,FALSE)=D3,"Yes","No") What I need it to do is basically look at all instances the Function value occurs in column A, and match them up (put 'X's) to all of the corresponding Teams. I realize this was not written very well, but any help will be greatly appreciated!! Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding multiple items from a list | Excel Worksheet Functions | |||
Create list of unique instances from list of multiple Instances | Excel Worksheet Functions | |||
Multiple instances of a named range, not in the list | Excel Discussion (Misc queries) | |||
Finding all instances of an ink color | Excel Discussion (Misc queries) | |||
Extracting/look up data from a list and select multiple instances | Excel Worksheet Functions |