• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching a 2D list of data against two excel columns inside an excel

rkr80

New Member
Hello,


Is it possible to perform a vlookup, or some other lookup function, in a 2D list of data against two excel columns that contain multiple values?


For example, I have an excel with 5 columns. Out of which Column C contains a list of BP_IDs. Some cells here have a repeated Staff ID and Column D contains a list of MBB_Names. Some cells here have a repeated MBB_Names too and column E has voting values in the form of "Yes","No" or "Abstain". Here basically MBB has to vote on each BP_ID with values "Yes","No" or "Abstain".


BP_ID MBB Name Created Vote

930201084637 Jef 9/30/10 Yes

930201084637 Pau 9/30/10 No

930201084637 Rob 9/30/10 Abstain

91201011002 Jef 9/30/10 Yes


I have created a column F where i have listed all the MBB names and Column G where i have singled out all the unique BP_Ids. I want to match these 2 columns F & G to the columns C & D and find out who are the MBBs that have not voted yet by comparing each BP_Id and MBB name as composite key to the columns C & D.


I hope this makes sense. If you need me to clarify let me know.


Many thanks,

Rasmi
 

Hui

Excel Ninja
Staff member
Rasmi

I have assumed your data starts in Row 3

and that you have the values "Yes","No" and "Abstain" in H2:J2

then in H3 you can use

H3: =SUMPRODUCT(1*($C$3:C20=$F3)*($D$3:D20=$G3)*($E$3:E20=H$2))

adjust row 20 to suit

copy across to I3:J3

copy H3:J3 down to the end of your unique values
 

rkr80

New Member
Hi Hui

Nice to have your reply but not the solution i wanted to have. If you could send me your email id, i will send you the excel for review? Then only you can understand the real issue i am struck with?
 

Hui

Excel Ninja
Staff member
Rasmi


1.

If you select columns A:E and apply a Filter

Does selecting the Vote Filter and only enable the No votes solve your problem ?


2.

Another way to tackle this maybe to use an Advanced Filter


in G1:K1 put

Code:
ID	Comments	BP_ID	MBB Name	Vote

In K2 put No


Then goto Advanced Filter

Copy to another location

List range: $A$1:$E$284

Criteria Range: $G$1:$K$2

Copy to: $P$1

Unique records only: Ticked


You can add more criteria if you want by listing below the Range G1:K1
 
Top