Comparing Two Columns And Producing a Result

dgaines

New Member
Joined
Sep 15, 2017
Messages
10
I'm trying to compare two columns, say "A" and "B", and if there is a match i'd like to produce the value "retired" in column C. I am really just wanting to work off column A and see if any values in column B match up with column A. Most the values are in a similar style to "abc123456" or can even just be numbers like "123456". The problem I am having is I'm really just wanting to compare the numbers, and if there is any match on column A then produce "retired" on column C. The numbers are not on the same rows, and I'm wanting to search the whole column to look for a match. I have tried different syntax's and even using Fuzzy Lookup(as recommended here before), but the problem I'm having with Fuzzy is it is seeing that the beginning is different(such as "abc123456" and "xyz123456") and it is skipping those and not matching it up in any aspect of ratio, even though all the numbers are in fact matches. Any help would be greatly appreciated, I feel like I have overlooked something. Thanks.
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi,

Can you show a few lines of possible data for Columns A and B, and what your expected results should be ?
 
Upvote 0
[TABLE="width: 250"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]abc00200200[/TD]
[TD="align: right"]103705[/TD]
[TD] retired[/TD]
[/TR]
[TR]
[TD]abc00200786[/TD]
[TD]abc00200778[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc00103789[/TD]
[TD]def00200828 [/TD]
[TD]retired[/TD]
[/TR]
[TR]
[TD]abc00200828[/TD]
[TD]abc00103789[/TD]
[TD]retired[/TD]
[/TR]
[TR]
[TD]abc230205[/TD]
[TD="align: right"]108134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc00200862[/TD]
[TD][/TD]
[TD]retired[/TD]
[/TR]
[TR]
[TD]def00102056[/TD]
[TD]abc00200862[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]def00103705[/TD]
[TD]def00200845[/TD]
[TD]retired[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]200200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

It looks like, and I'm assuming, you're only interested in matching the 6 digits on the right of Column A, if it's Anywhere in Column B, then result "retired", otherwise Blank, see if this works for your data:


Book1
ABC
1abc00200200103705retired
2abc00200786abc00200778
3abc00103789def00200828retired
4abc00200828abc00103789retired
5abc230205108134
6abc00200862retired
7def00102056abc00200862
8def00103705def00200845retired
9200200
Sheet449
Cell Formulas
RangeFormula
C1=IF(A1="","",IF(ISNUMBER(LOOKUP(2,1/FIND(RIGHT(A1,6),B$1:B$9))),"retired",""))
 
Upvote 0
That is correct. Basically I'm just trying to see of there are any number matches, wherever they may be in the column. I tried that out, but i will say while what I posted is a sample I'm actually working with over 4000 rows :eeek: and it doesn't look to getting all them
 
Upvote 0
Well, if it works for 4 rows, it'll work for 40, 400, 4000 or more rows.

So, if you say
it doesn't look to getting all them

Can you show samples of Column A and Column B data where it Doesn't work ?
 
Upvote 0
O ok, sorry. I gotcha. Then it looks to just not be throwing any hits. I'm not getting any showing "retired" on column C. It is not throwing an error either though, and looks like it applied correctly to the whole column. It's just all blank on column C.
 
Upvote 0
Can you Confirm that there's in fact a Match in Column B for a value in Column A, but corresponding Column C is Blank?

If so, can you show some sample data where the formula is showing Blank instead of "retired" ?
 
Upvote 0
I got it to work!!!! I had to put =IF(A1="","",IF(ISNUMBER(LOOKUP(2,1/FIND(RIGHT(A1,6),B$1:B$1710))),"retired",""))

Just had to edit that last number on the B column but it's definitely working now. Thank you because I definitely couldn't have gotten that without your help!
 
Upvote 0
Yes, you would have to adjust the Cell references/range in the formula to match your data...

So I'm glad you got it, and you're welcome.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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