compare array help

[Tootlez]

I tried doing this in a simple isnumber-search, but i have over 64 cells to compare. - this worked for my other project that was only like 40.

I need to see if any of the values in column b are in column a then return column c into column d

i tried vlookup, lookup and stupidly a simple search.

Any clue how to do this? -- I'm not into vba right now no need to get into it either.

Thanks

 

[Aladin Akyurek]

That did return values, but it didn't return the proper C to the A

What that did was if it found B in ANY A it returned C.

I need it to return the proper C for the B found in A. So reguardless of what the B/C is next to A I need it to return whatever the code's finish is that A possesses.


What the formula should do is this:
-----A------------------- = ---------D

ML-177-301	=	Castlewood Walnut with Silver Highlights
ML-8609-A179-PL	=
ML-9803-143-PL	=	Oil Rubbed Bronze
ML-9803-144-PL	=	Bruhsed Stainless Steel
ML-341-172	=	Smoked Iron
ML-823-77	=	Chrome
ML-823-84	=	Brushed Nickel
ML-351	=
ML-830-84-PL	=	Brushed Nickel
ML-860-84-PL	=	Brushed Nickel

<tbody>
</tbody>

You are confusing... Why did you claim that

84 ---> Brushed Nickel ?

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

In D2 

=IFERROR(LOOKUP(9^9,SEARCH("-"&$B$2:$B$53&"-","-"&$A2&"-"),$C$2:$C$53),"")

And copy down.

Markmzz

 

[Tootlez]

sorry i'm not trying to confuse you...

yes 84 = Brushed Nickel

B=C always, and A contains B and I want to return C for every B that is in A.

Like the first entry A contains 301, which is Castlewood Walnut, so I need that to be returned by my formula in Column D.

Originally what I started to do was this:
= if( isnumber(search("-"&B2", A2), C2, [and did a nested if just changing the B's and C's]

But the problem is that I have over 64, so I couldn't finish my formula, so i figured I could solve that with it looking through an array to do the same thing...

 

[Tootlez]

Mark, Yes! that works

I was curious what the 9^9 is for though?
I tried something like that earlier, but I guess I missed something somewhere...

 

[markmzz]

Mark, Yes! that works

I was curious what the 9^9 is for though?
I tried something like that earlier, but I guess I missed something somewhere...

I'll try to explain with a table:

sku	Code	Finish	Result	ML-177-301	ML-9803-143-PL	ML-9803-144-PL	ML-341-172	ML-1000-44-PL
ML-177-301	8	Antique Iron	Castlewood Walnut with Silver Highlights	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-8609-A179-PL	14	Nutmeg		#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-9803-143-PL	37	Restoration Bronze	Oil Rubbed Bronze	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-9803-144-PL	37B	Dark Restoration Bronze	Brushed Stainless Steel	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-341-172	37C	Restoration Bronze without Gold Highlights	Smoked Iron	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-823-77	39	Spanish Iron		#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-823-84	44	White		#VALOR!	#VALOR!	#VALOR!	#VALOR!	9
ML-1000-44-PL	138	Aspen Bronze	White	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1001-44-PL	143	Oil Rubbed Bronze	White	#VALOR!	9	#VALOR!	#VALOR!	#VALOR!
ML-664-PL	144	Brushed Stainless Steel		#VALOR!	#VALOR!	9	#VALOR!	#VALOR!
ML-1001-126-PL	156	Noble Bronze		#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-668-PL	164	Heritage with Silver Highlights		#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1016-44-PL	166	Corona Bronze	White	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1017-44-PL	167	Lathan Bronze	White	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1018-44-PL	167B	Deep Lathan Bronze	White	#VALOR!	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1134-84	172	Smoked Iron		#VALOR!	#VALOR!	#VALOR!	8	#VALOR!
ML-721-355	177	Illuminati Bronze		4	#VALOR!	#VALOR!	#VALOR!	#VALOR!
ML-1422-281	301	Castlewood Walnut with Silver Highlights		8	#VALOR!	#VALOR!	#VALOR!	#VALOR!
			=IFERROR(LOOKUP(9^9,SEARCH("-"&$B$2:$B$19&"-","-"&$A2&"-"),$C$2:$C$19),"")	Found 8	Found 9	Found 9	Found 8	Found 9
		PS - VALOR is VALUE and 9^9 is a big number in this case (work with text)	The 9^9 is to find the last number in the array (Lookup function ignores errors)	Search part	Search part	Search part	Search part	Search part
*****************	*****	*****************************************	******************************************************************************	****************	****************	****************	****************	****************

<tbody>
</tbody>

I hope that the table above helps.

Markmzz

 

[Tootlez]

It does to a point, I just like to understand what I'm using, and why it works.

Is the 9 always the case or does it depend on what data is being used for the number needed?

 

[Aladin Akyurek]

sorry i'm not trying to confuse you...

yes 84 = Brushed Nickel

B=C always, and A contains B and I want to return C for every B that is in A.

Like the first entry A contains 301, which is Castlewood Walnut, so I need that to be returned by my formula in Column D.

Originally what I started to do was this:
= if( isnumber(search("-"&B2", A2), C2, [and did a nested if just changing the B's and C's]

But the problem is that I have over 64, so I couldn't finish my formula, so i figured I could solve that with it looking through an array to do the same thing...

You still do... One of:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Brange,A2),Crange),"")

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("-"&Brange&"-",A2&"-"),Crange),"")

See for the big number:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

 

[Aladin Akyurek]

It does to a point, I just like to understand what I'm using, and why it works.

Is the 9 always the case or does it depend on what data is being used for the number needed?

See the links I quoted...

 

[markmzz]

It does to a point, I just like to understand what I'm using, and why it works.

Is the 9 always the case or does it depend on what data is being used for the number needed?

The numbers reflect the positions of the data in the column B in given the respective cell of the column A (for example: the 301 of the cell B19 starts on the eighth (8) of the given cell A2).

Markmzz

 

[Tootlez]

So it ends up being nearly the same - between the two of you.

Thanks for the help! Sorry to be confusing. But I'm glad I got it figured out