compare array help

H

hippiehacker

Well-known Member

Joined

Aug 2, 2011

Messages

1,911

• Mar 3, 2014

• #2

this in D1 =IF(NOT(ISERROR(MATCH(B1;A:A;0)));C1;"")

it will check if B1 can be found in Col A and return C1 if found

did i understand correctly??

 

Upvote 0

Trouttrap2

Well-known Member

Joined

May 11, 2010

Messages

612

• Mar 3, 2014

• #3

you can try this. Enter into D1 and drag down.
=VLOOKUP(B1,A:C,3,0)

 

Upvote 0

T

Tootlez

Board Regular

Joined

Jan 30, 2014

Messages

61

• Mar 3, 2014

• #4

neither of those seem to work.

a quick example:
my A2 contains B53, and A16 contains b13 etc ...
And some i know don't match, which i can manually fix.

but if the range changes, it won't catch that the value above the current cell is in the column.

 

Upvote 0

H

hippiehacker

Well-known Member

Joined

Aug 2, 2011

Messages

1,911

• Mar 3, 2014

• #5

can you post some sample data I'm lost now???

 

Upvote 0

T

Tootlez

Board Regular

Joined

Jan 30, 2014

Messages

61

• Mar 3, 2014

• #6

hope this helps - i don't know how to post the table so things are in columns, but i hope this works...I'll edit if some one tells me how to format it

and mind you that i have nearly 1000 skus, but only 73 of the code/finishes - so the range has to stay the same so i don't loose info
(and I tried to grab as many different examples to help see what i need)

sku	Code	Finish
ML-177-301	8	Antique Iron
ML-8609-A179-PL	14	Nutmeg
ML-9803-143-PL	37	Restoration Bronze
ML-9803-144-PL	37B	Dark Restoration Bronze
ML-341-172	37C	Restoration Bronze without Gold Highlights
ML-823-77	39	Spanish Iron
ML-823-84	44	White
ML-351	56	Antique Nickel
ML-830-84-PL	61	Vintage Rust
ML-860-84-PL	66	Black
ML-863-84-PL	77	Chrome
ML-867-44-PL	84	Brushed Nickel
ML-412	86	Textured White
ML-414	91	Antique Bronze
ML-891-84-PL	94	Heritage
ML-892-84-PL	126	Belcaro Walnut
ML-1000-44-PL	138	Aspen Bronze
ML-1001-44-PL	143	Oil Rubbed Bronze
ML-664-PL	144	Brushed Stainless Steel
ML-1001-126-PL	156	Noble Bronze
ML-668-PL	164	Heritage with Silver Highlights
ML-1016-44-PL	166	Corona Bronze
ML-1017-44-PL	167	Lathan Bronze
ML-1018-44-PL	167B	Deep Lathan Bronze
ML-1134-84	172	Smoked Iron
ML-721-355	177	Illuminati Bronze
ML-1137-84	179	Textured French Bronze
ML-1139-84	196C	Tuscan Patina
ML-731-355	206	Aston Court Bronze
ML-770-301	207	Distressed Silver
ML-772-301	209	Distressed Bronze
ML-773-301	211	Tofino Bronze
ML-775-301	216	Cabella Patina
ML-776-301	243	Raffine Aged Patina
ML-778-301	244	Aged Stone with Travertine Stone
ML-779-301	248	Honey Gold
ML-1233-580	249	Liberty Gold
ML-1233-648	252	Nanti Champagne Silver
ML-828-84-PL	267	Dark Brushed Bronze
ML-1234-580	267B	Dark Brushed Bronze
ML-828-91	267C	Dark Brushed Bronze
ML-1279-172	273	Aged Patina Iron with Travertine Stone
ML-1291-580	281	Harvard Court Bronze
ML-829-91	282	Royal Oak Patina
ML-829-91-PL	288	Deep Flax Bronze
ML-1291-648	289	Vintage Cheshire Gold
ML-1297-580	290	Classic Oak Patina
ML-830-91	292	Terzetto Bronze
ML-841-84	296	Elcanté Patina Bronze
ML-861-84-PL	298	Aged Kinston Bronze
ML-1420-281	299	Regents Patina
ML-1422-281	301	Castlewood Walnut with Silver Highlights

<tbody>
</tbody>

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 3, 2014

• #7

What is the expected result for the B-value of 84, that is, what do you want to see in the corresponding D-cell?

 

Upvote 0

T

Tootlez

Board Regular

Joined

Jan 30, 2014

Messages

61

• Mar 3, 2014

• #8

I need to see if B is in A and then I need the respective C to be returned in the formula cell (D)

Columns B and C are linked, the value is B is always going to be the same value in C when B is present.

and all I'm wanting to do is: If B is in A then return C

And so if A is "ML-892-84-PL" and it contains the Bx value of "84" then I need the corresponding C value of "Brushed Nickel" to be returned in column D.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Mar 3, 2014

• #9

Tootlez said:

I need to see if B is in A and then I need the respective C to be returned in the formula cell (D)

Columns B and C are linked, the value is B is always going to be the same value in C when B is present.

and all I'm wanting to do is: If B is in A then return C

And so if A is "ML-892-84-PL" and it contains the Bx value of "84" then I need the corresponding C value of "Brushed Nickel" to be returned in column D.

Click to expand...

Does this...

=IF(ISNUMBER(MATCH("*-"&$B2&"-*",A:A,0)),$C2,"")

help?

 

Upvote 0

T

Tootlez

Board Regular

Joined

Jan 30, 2014

Messages

61

• Mar 3, 2014

• #10

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>

 

Upvote 0