Not sure which function and how to use it. Help

[Damon Johnson]

Hello Everyone,

I’m trying to change all the customer id’s in column A to the corresponding
ship to id’s in column F. There’s a one to many relationship between F and A.
I need a formula that will look up the values in column A. Then look for a
matching number in column D. When it finds a matching number between column
A and D, take the ship to id in column F that is on the same row as the
matching customer id in column D and place it on the appropriate
row in column C.




customer_id A customer_name B C customer_id D customer_name E ship_to_id F
1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267
1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278
1290 PARTS UNLIMITED 1266 Scott Equipment 24289
1290 PARTS UNLIMITED 1267 Tiger Machinery 24300
1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311
1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312
1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313
1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314
1295 EVERGREEN SUPPLY


customer_id A customer_name B C customer_id D customer_name E ship_to_id F
1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267
1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278
1290 PARTS UNLIMITED 1266 Scott Equipment 24289
1290 PARTS UNLIMITED 1267 Tiger Machinery 24300
1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311
1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312
1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313
1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314
1295 EVERGREEN SUPPLY
So in this example what I would end up with is this;

customer_id A customer_name B C customer_id D customer_name E ship_to_id F
1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267
1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278
1290 PARTS UNLIMITED 1266 Scott Equipment 24289
1290 PARTS UNLIMITED 1267 Tiger Machinery 24300
1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311
1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312
1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313
1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314
1295 EVERGREEN SUPPLY

customer_id A customer_name B C customer_id D customer_name E ship_to_id F
1288 INDUSTRY TECNOLOGICAL 24312 1264 Quality Hyd. and Machining 24267
1288 INDUSTRY TECNOLOGICAL 24312 1265 Northside Implement 24278
1290 PARTS UNLIMITED 24313 1266 Scott Equipment 24289
1290 PARTS UNLIMITED 24313 1267 Tiger Machinery 24300
1290 PARTS UNLIMITED 24313 1268 JR's Construction Parts, Inc. 24311
1295 EVERGREEN SUPPLY 24314 1288 INDUSTRY TECNOLOGICAL 24312
1295 EVERGREEN SUPPLY 24314 1290 PARTS UNLIMITED 24313
1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314
1295 EVERGREEN SUPPLY 24314

I pasted this example from excel. Sorry if the colums do not line up. CAn
anyone tell me how I can better align the columns so that this example is
better to understand?

 

[bj]

try
=index(F:F,match(A1,D,0),0,1,1)
in C1 and copy down

 

[Damon Johnson]

WOW!!!
bj, thanks sooo much. I've been wearing myself trying to figure this out.
It worked perfectly.

Again, many thanks!!!
Damon