?
Solved

match formula

Posted on 2014-01-27
5
Medium Priority
?
289 Views
Last Modified: 2014-01-27
I have an xlsx with 2 sheets. In column G of sheet 1 are a list of names, in column H I have put a column called "match". On sheet 2 is also a column (column A) full of names. What formula can I put in column H to put a 1 if the name in column G sheet 1 is also evident in the list of names on sheet 2 column A, and a 0 if it isn't evident?
0
Comment
Question by:pma111
5 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 668 total points
ID: 39811816
=sign(countif(sheet2!A:A,g2))
0
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 668 total points
ID: 39811817
use VLOOKUP formula
if you want to have O and 1 then you can add in sheet 2 column B with 1´s and use VLOOKUP with IFERROR together - as you can see i the attached sample
sample.xlsx
0
 
LVL 3

Author Comment

by:pma111
ID: 39811835
top formula creates errors
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 664 total points
ID: 39811849
See the example attached.

Sincerely,

Ed
Sample.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39811852
It works here
Match-by-count.xlsx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

864 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