1. This is an oversimplified example but gets the point of my problem across – I hope ;-).
2. I have two “sets” of data A & B, with 3 columns each in a spreadsheet as Data Set A=columns A,B & C and Data Set B= Columns D, E & F.
3. Column A&D are UPC numbers, B&E are Description and C&F are Cost – none sorted in any order.
4. I need to take the UPC# in Column A, MATCH it with the same UPC# in Column D and get the row number of the match in Column D.
5. This works fine with the MATCH function i.e. I can find that the UPC# in “row 11 Column A” is the same UPC# as “row38 Column D”
6. MATCH(A11,D:D,0) returns 38
7. Here is where I fall off a cliff! I want to replace/update the OLD COST in Data Set B – Column D Row 38 with the NEW COST from Data Set A row 11 – Column C. All in a formula that never leaves row 11!
8. Restated: After my MATCH statement I know I want to replace/update the OLD cost in F38 with the NEW cost in C11 – using ONLY a formula with info I have in row 11.
9. Next I’ll go to A12 and it’s MATCH my be D9
10. Next I’ll go to A13 and it’s MATCH my be D45
11. Next I’ll go to A14 and it’s MATCH my be D23
12. You get the idea – a formula that takes the unsorted A column UPC#’s, MATCHES it with the unsorted UPC#’s in column C – then replaces the OLD COST with the new one. And at NO MATCH it just skips and goes on.
My fantasy formula would be:
If (MATCH is an Integer – i.e. a MATCH was found)
TRUE: Take the NEW COST in Column C, current row and over write the OLD COST in Column D row (from the MATCH function)
FALSE: Print “Flag New Item”
Thanks for any help,
Royce
PS: Using Excel 2k on an XP box
Bookmarks