Solved

excel lookup checking for value in another tab

Posted on 2014-09-12
2
57 Views
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
0
Comment
Question by:Matt Pinkston
2 Comments
 
LVL 27

Accepted Solution

by:
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

by:pjg418
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

708 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

15 Experts available now in Live!

Get 1:1 Help Now