excel lookup checking for value in another tab

Posted on 2014-09-12
Last Modified: 2014-09-30
I have a simple XLS with two tabs GROUP & USERS

I want column I of users to be Yes when column B of USERS is found in column B of GROUP.  If it is not found I would like that column to be No
Question by:Matt Pinkston
LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40320189
On the Users sheet - in Column I - insert this in the first cell (assuming I2 to start) and copy down:


Expert Comment

ID: 40320203
This is done with a Formatting rule or without, i liked using formatting rules, but they are more difficult.

You will select a range for your formatting rule to apply to which will be the Column 1 of Users.

Your rule will look at the users!$b(row#)  for a value (the name) and then determine if it is a member of the collection Column B of Group. You could use count IF for this


You will wrap that above statement in an if statement. If the statement resolves to greater than 0 it means that it found the item in the collection of GROUP!B:B. Set the if yes value to "YES" and the fail value to "no"

that will write either yes or no in your row if the item exists in the range on the second sheet.


True, you could write that countIf value into another column that you hide (like column z) then in column USERS!A you could write something life if($z(row#) > 0, 'yes', 'no') which will have the same effect without having to worry about using a formatting rule


in deed you could write the countIf formula into the first half of the if and then you don't need two columns (TRUE)

If(((COUNTIF(USERS!B:B, $B1) > 0), 'yes', 'no')

My excel is rusty, if in excel is IIF i think, anyways lmk

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 32
InternetExplorer object in Excel VBA. 4 22
Sum iF  based on a null cell 11 29
Auto Adjust Percent rate 5 31
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now