Comparing Two Columns And Producing a Result

[dgaines]

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.

 

[jtakw]

Hi,

Can you show a few lines of possible data for Columns A and B, and what your expected results should be ?

 

[dgaines]

abc00200200	103705	retired
abc00200786	abc00200778
abc00103789	def00200828	retired
abc00200828	abc00103789	retired
abc230205	108134
abc00200862		retired
def00102056	abc00200862
def00103705	def00200845	retired
	200200

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[jtakw]

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
	A	B	C
1	abc00200200	103705	retired
2	abc00200786	abc00200778
3	abc00103789	def00200828	retired
4	abc00200828	abc00103789	retired
5	abc230205	108134
6	abc00200862		retired
7	def00102056	abc00200862
8	def00103705	def00200845	retired
9		200200
Sheet449

Cell Formulas
Range	Formula
C1	=IF(A1="","",IF(ISNUMBER(LOOKUP(2,1/FIND(RIGHT(A1,6),B$1:B$9))),"retired",""))

 

[dgaines]

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 and it doesn't look to getting all them

 

[jtakw]

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 ?

 

[dgaines]

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.

 

[jtakw]

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" ?

 

[dgaines]

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!

 

[jtakw]

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.