SOME LOGICAL HELP

[KEVATMAH]

COLUMN A IS 450 ITEM NUMBERS DEEP
COLUMN B IS A DESCRIPTIVE OF EACH
COLUMN C IS A CUSTOM NUMER (OUR INTERNAL) CORRESPONDING TO A
COLUMN D IS 9500 ITEM NUMBERS DEEP (MFR TOTAL PRODUCT LIST)
COLUMN E IS A DESCRIPTIVE OF EACH OF THE 9500
BURIED WITHIN THE 9500 IN NO PARTICULAR ORDER IS THE 450 FROM A
I WANT TO WRITE A MACRO/FORMULA TO SORT THROUGH ALL 9500 LOOKING FOR ONLY OUR ITEMS. WHERE THERE IS A MATCH, I WANT TO PASTE OUR NUMBER, IN AN AJOINING COLUMN.
ESSENTIALLY
IF WHERE A=D, THEN COPY C INTO COLUMN F.
REPEAT FOR ALL 450 ITEMS (AUTOMATICALLY)

 

[just_jon]

COLUMN A IS 450 ITEM NUMBERS DEEP
COLUMN B IS A DESCRIPTIVE OF EACH
COLUMN C IS A CUSTOM NUMER (OUR INTERNAL) CORRESPONDING TO A
COLUMN D IS 9500 ITEM NUMBERS DEEP (MFR TOTAL PRODUCT LIST)
COLUMN E IS A DESCRIPTIVE OF EACH OF THE 9500
BURIED WITHIN THE 9500 IN NO PARTICULAR ORDER IS THE 450 FROM A
I WANT TO WRITE A MACRO/FORMULA TO SORT THROUGH ALL 9500 LOOKING FOR ONLY OUR ITEMS. WHERE THERE IS A MATCH, I WANT TO PASTE OUR NUMBER, IN AN AJOINING COLUMN.
ESSENTIALLY
IF WHERE A=D, THEN COPY C INTO COLUMN F.
REPEAT FOR ALL 450 ITEMS (AUTOMATICALLY)

If I understand correctly, in F1 copied down

=INDEX(C:C,MATCH(D1,A:A,0))

EDIT Welcome to MrExcel.

 

[KEVATMAH]

some logical help

your formula worked....beautiful.

items which do not have a match come up as #N/A. which is fine.

If you are bored, can you explain to me what exactly is happening here....? falls under the "give a man a fish, feed for a day...teach him to fish...." category.

Thank you all the same, been trying to get this for hours now.

 

[just_jon]

Re: some logical help

your formula worked....beautiful.

items which do not have a match come up as #N/A. which is fine.

If you are bored, can you explain to me what exactly is happening here....? falls under the "give a man a fish, feed for a day...teach him to fish...." category.

Thank you all the same, been trying to get this for hours now.

OK, you are using a combination of the INDEX and MATCH functions.

INDEX has the structure: INDEX(Range, RowRef, ColRef)
Where Range is the range of interest, RowRef tells it what ordinal - 1st, 2nd,...nth - row to get its value, while ColRef does the same for the column within Range.

So we have: C:C as our RangeRef

and we have: MATCH(D1,A;A,0) as our RowRef

and we have: omitted the ColRef -- if omitted, it defaults to 1 and as our range of interest is just a single column [C] that suits us just fine.

Now MATCH has the structure:

MATCH(ThisValue, WithinThisRange, MatchType) -- returns the ordinal row in which found

The value we are seeking is D1, and we want to find the place it is found in column A. The MatchType of 0 means "find EXACT match only", as opposed to a 1 which would mean approximate match.

So what you get is the row in A at which D1 is found being fed in INDEX as INDEX's 2nd argument, the RowRef.

 

[KEVATMAH]

Thanks...for the explanation

I think I understand. Thanks for taking the time.