Updating a value using match

RoccoM

New Member
Joined
Apr 22, 2019
Messages
19
Column X contains a list of all companies.
Column Y is used for a simple check (values would be yes, no, n/a)
In column Z I have a partial list of company names.

Here is what I need help with:
I need to start at Cell z2 see if that value matches ANY value in my column X Range (x1:x1000)
If yes I need to update value in column b with a yes

How can I do this?

Thank you in advance.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Maybe...

BWXYZ
Update HereCompany ListSimple CheckPartial List
yesWhite Sky Inc.yesACME Widgets
yesEasy PartnersnoIvor Leake, Plumber
ACME Widgetsn/a
Hope & Sky Inc.yes
Lawyers Soo, Grabbitt & Runnno
Ivor Leake, Plumbern/a

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
RoccoM

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2
[/TH]
[TD="align: left"]=IF(ISNA(MATCH(Z2,$X$2:$X$1000,0)),"","yes")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Team:
I didn't explain my self correctly. But based on your answers to my question, I'm so close I could taste it. :)

It is important where the yes answer goes.

If a value in a cell in column Z matches a value in column X then I need the column Y to say yes for the corresponding value of X.
For example:

I find Acme Widgets in my full list of companies in column X, I then update the simple check column, column, Y next to Acme Widgets with Yes
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]Y[/TD]
[TD="class: xl65, width: 64"]Z[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Company List[/TD]
[TD="class: xl67, width: 64"]Simple Check[/TD]
[TD="class: xl67, width: 64"]Partial List[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]2[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]White Sky Inc.[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl69, width: 64"]ACME Widgets[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]3[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Easy Partners[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl69, width: 64"]Ivor Leake, Plumber[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]4[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"]ACME Widgets[/TD]
[TD="class: xl68, width: 64"]Yes[/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]5[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Hope & Sky Inc.[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]6[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Lawyers Soo, Grabbitt & Runn[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]7[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"]Ivor Leake, Plumber[/TD]
[TD="class: xl68, width: 64"]Yes[/TD]
[TD="class: xl67, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
I hope this makes more sense?
Rocco
 
Upvote 0
Is this it?

XYZ
Company ListSimple CheckPartial List
White Sky Inc.ACME Widgets
Easy PartnersIvor Leake, Plumber
ACME Widgetsyes
Hope & Sky Inc.
Lawyers Soo, Grabbitt & Runn
Ivor Leake, Plumberyes

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

</tbody>
RoccoM

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y2 to
Y1000
[/TH]
[TD="align: left"]=IF(ISNA(MATCH(X2,$Z$2:$Z$1000,0)),"","yes")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,224,746
Messages
6,180,705
Members
452,994
Latest member
Janick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top