Solved

# excel lookup checking for value in another tab

Posted on 2014-09-12
63 Views
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
0
Question by:Matt Pinkston
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

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:
=IF(COUNTIF(Group!B:B,B2)>0,"Yes","No")

Regards,
-Glenn
0

LVL 1

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

COUNTIF(USERS!B:B, \$B1)

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.

BUT PETER THERE MUST BE ANOTHER WAY!!!!

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

BUT PETER THERE MUST BE ANOTHER WAY!!!!

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
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…