Excel: How to find name in sheet and mark “yes” on another column

up vote
-1
down vote

favorite

Let's say I have a excel sheet like this one below just with many more names

and I have another list where I have a certain number of names. How do I make a code so that if I input in the names from the other sheet, and if it matches 70%
(or 100% if that is not possible) with the names on the first sheet, then a "yes" will be printed on column D.

like let's say I want to find "John" and "Jerry" on the list, and I put it in the formula, then only a "yes" will be marked after "John" since the latter name is not in the list?

asked Nov 19 at 13:17

user35897

1013

1

For a fuzzy match this might help: Fuzzy Matching. For a 100% matching have a look at FIND - there's plenty of examples on SO.
– Darren Bartrup-Cook
Nov 19 at 13:28

add a comment |

up vote
-1
down vote

favorite

Let's say I have a excel sheet like this one below just with many more names

like let's say I want to find "John" and "Jerry" on the list, and I put it in the formula, then only a "yes" will be marked after "John" since the latter name is not in the list?

asked Nov 19 at 13:17

user35897

1013

1

For a fuzzy match this might help: Fuzzy Matching. For a 100% matching have a look at FIND - there's plenty of examples on SO.
– Darren Bartrup-Cook
Nov 19 at 13:28

add a comment |

up vote
-1
down vote

favorite

Let's say I have a excel sheet like this one below just with many more names

like let's say I want to find "John" and "Jerry" on the list, and I put it in the formula, then only a "yes" will be marked after "John" since the latter name is not in the list?

asked Nov 19 at 13:17

user35897

1013

Let's say I have a excel sheet like this one below just with many more names

like let's say I want to find "John" and "Jerry" on the list, and I put it in the formula, then only a "yes" will be marked after "John" since the latter name is not in the list?

excel

asked Nov 19 at 13:17

user35897

1013

asked Nov 19 at 13:17

user35897

1013

asked Nov 19 at 13:17

user35897

1013

asked Nov 19 at 13:17

user35897

1013

asked Nov 19 at 13:17

user35897

1013

1

For a fuzzy match this might help: Fuzzy Matching. For a 100% matching have a look at FIND - there's plenty of examples on SO.
– Darren Bartrup-Cook
Nov 19 at 13:28

add a comment |

1

For a fuzzy match this might help: Fuzzy Matching. For a 100% matching have a look at FIND - there's plenty of examples on SO.
– Darren Bartrup-Cook
Nov 19 at 13:28

For a fuzzy match this might help: Fuzzy Matching. For a 100% matching have a look at FIND - there's plenty of examples on SO.
– Darren Bartrup-Cook
Nov 19 at 13:28

add a comment |

2 Answers
2

active

oldest

votes

up vote
1
down vote

accepted

Try this:

IF(IFERROR(MATCH(A2,$H$2:$H$5,0)>0,FALSE),"Yes","No")

See:
enter image description here

The list in column H can be on another sheet, just drag down to check cells in col A.

answered Nov 19 at 13:41

Solar Mike

1,9072514

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

|
show 2 more comments

up vote
1
down vote

Like this?

Assuming "John" is in cell Aé

Assuming your list is on Sheet2, column A

Using , as delimiter

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"","yes")

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375494%2fexcel-how-to-find-name-in-sheet-and-mark-yes-on-another-column%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
1
down vote

accepted

Try this:

IF(IFERROR(MATCH(A2,$H$2:$H$5,0)>0,FALSE),"Yes","No")

See:
enter image description here

The list in column H can be on another sheet, just drag down to check cells in col A.

answered Nov 19 at 13:41

Solar Mike

1,9072514

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

|
show 2 more comments

up vote
1
down vote

accepted

Try this:

IF(IFERROR(MATCH(A2,$H$2:$H$5,0)>0,FALSE),"Yes","No")

See:
enter image description here

The list in column H can be on another sheet, just drag down to check cells in col A.

answered Nov 19 at 13:41

Solar Mike

1,9072514

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

|
show 2 more comments

up vote
1
down vote

accepted

Try this:

IF(IFERROR(MATCH(A2,$H$2:$H$5,0)>0,FALSE),"Yes","No")

See:
enter image description here

The list in column H can be on another sheet, just drag down to check cells in col A.

answered Nov 19 at 13:41

Solar Mike

1,9072514

Try this:

IF(IFERROR(MATCH(A2,$H$2:$H$5,0)>0,FALSE),"Yes","No")

See:
enter image description here

The list in column H can be on another sheet, just drag down to check cells in col A.

answered Nov 19 at 13:41

Solar Mike

1,9072514

answered Nov 19 at 13:41

Solar Mike

1,9072514

answered Nov 19 at 13:41

Solar Mike

1,9072514

answered Nov 19 at 13:41

Solar Mike

1,9072514

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

|
show 2 more comments

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

so select all values in a and then enter formula and then press cntrl enter?
– user35897
Nov 19 at 13:47

It’s not an array formula, try it.
– Solar Mike
Nov 19 at 13:56

Ok just tried it but then I could only go one box each time. Also is it possible to get a "yes" for a 70% match?
– user35897
Nov 19 at 14:11

How will you calculate 70%? 3 letters out of 4 is 75%...
– Solar Mike
Nov 19 at 14:13

3 letters in order =75%
– user35897
Nov 19 at 14:15

|
show 2 more comments

up vote
1
down vote

Like this?

Assuming "John" is in cell Aé

Assuming your list is on Sheet2, column A

Using , as delimiter

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"","yes")

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

add a comment |

up vote
1
down vote

Like this?

Assuming "John" is in cell Aé

Assuming your list is on Sheet2, column A

Using , as delimiter

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"","yes")

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

add a comment |

up vote
1
down vote

Like this?

Assuming "John" is in cell Aé

Assuming your list is on Sheet2, column A

Using , as delimiter

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"","yes")

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

Like this?

Assuming "John" is in cell Aé

Assuming your list is on Sheet2, column A

Using , as delimiter

=IF(ISERROR(MATCH(A2,Sheet2!A:A,0)),"","yes")

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

edited Nov 19 at 13:59

Darren Bartrup-Cook

13.3k11431

answered Nov 19 at 13:40

Lambik

403410

answered Nov 19 at 13:40

Lambik

403410

answered Nov 19 at 13:40

Lambik

403410

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr