Advanced Vlookup (advanced to me at least) URGENT

[Davidmcho]

Hello,  I need to do a vlookup to do some data validation. 

(VLOOKUP(AL3,'Clients'!$B:$B,1,0)<>"","Yes","No")

This is what I need. 

AL3 and $B:$B are in different tabs of an excel spreadsheet.

AL3 has a column header of "Group Name" and so does column B of the "Client" tab. 

I tried using a Join, but this screws up the data if there are duplicate values in Column B. 

What I'm trying to do is check in Column B if the group name from AL3 (column A of Tab X) exists in Column B.

Please help me ... if you need help clarifying as I did a terrible job explaining, please don't hesitate to let me know.

Thank you all!

[MarqueeCrew · Accepted solution]

Urgent/Quick Response....

1. Input 1:  AL3
2. Input 2: B:
3. Summarize input 2 and GROUP BY Group Name.  This gets you a single value for all unique group names.
4. Take the output from input 1 and the output from the summarize and connect them to a FIND & Replace (F= input1, R=Summarize).
   1. Find within field GroupName
   2. Find Value field GroupName
   3. Find: Entire Field
   4. Case Insensitive if you need it
   5. Match whole word if you need it
   6. APPEND field(s) to Record: GroupName

This will create a [Group Name2] field because it will rename the found value.  When it finds something, it will be NOT-Null, if it doesn't it will be null.

Use a formula tool as:

IIF(IsNull([Group Name2]),"No","Yes")

Cheers,

Mark

[Davidmcho]

You're my hero, thank you so much

[MarqueeCrew]

That’s awfully nice. My pleasure.

[Davidmcho]

Mark, if this becomes more complex and it ends up becoming an index and match function such as:

IF(INDEX('Client'!$A:$I,MATCH($AI3&$AL3,'Client'!$A:$A&'Client'!$B:$B,0),2)<>"","Yes","No")

How would this be possible? the Find and Replace tool doesn't seem to work here since the index function in excel is looking at multiple columns not just one.

[MarqueeCrew]

You can transpose the data columns and group by the output value column. That’s the trick there.

[Davidmcho]

There is also Two Lookup values in the match portion of the function and two lookup array columns. This makes it even more confusing...... 

IF(INDEX('Client'!$A:$I,MATCH($AI3&$AL3,'Client'!$A:$A&'Client'!$B:$B,0),2)<>"","Yes","No")

[Davidmcho]

Let me give that a try. Thank you for your quick response! You have my utmost gratitude 

[Davidmcho]

Mark, I'm completely lost... Is there any way you can show me how to do and take into consideration there is two lookup values in the Match formula and two columns in the lookup array? 

This is my last task ............. Please.

IF(INDEX('Client'!$A:$I,MATCH($AI3&$AL3,'Client'!$A:$A&'Client'!$B:$B,0),2)<>"","Yes","No")