Im trying to create a list of names from a range based on two variables, while also eliminating duplicate results or blank spaces. For example, if i want to know who has apples OR oranges:
Huey Apples
Dewey Pears
Louie Oranges
Should return:
Huey
Louie
I think a INDEX MATCH in combination with an if/or should work, but I can't seem to put it together correctly....
Thanks!
Try this...
Data Range
This array formula** entered in F2:
=IFERROR(INDEX(A$2:A$10,MATCH(0,IF(A$2:A$10<>"",IF(ISNUMBER(MATCH(B$2:B$10,D$2:E$2,0)),COUNTIF(F$1:F1,A$2:A$10))),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
You are an excel God, and I revere you. Thank you.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks