Importing Excel data from another Excel file

%3CLINGO-SUB%20id%3D%22lingo-sub-3219424%22%20slang%3D%22en-US%22%3EImporting%20Excel%20data%20from%20another%20Excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219424%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20Excel%20file%20that%20I%20use%20to%20keep%20up%20with%20pricing%2C%20this%20file%20is%20represented%20by%20the%20first%20attached%20picture.%20I%20also%20get%20a%20file%20from%20my%20supplier%20every%20month%20that%20gives%20me%20the%20updated%20cost%20of%20items%20for%20the%20month.%20This%20file%20is%20represented%20by%20the%20second%20attached%20picture.%20I%20have%20been%20going%20through%20and%20manually%20changing%20the%20cost%20price%2C%20but%20lately%20the%20number%20of%20items%20on%20my%20list%20has%20grown%20to%20over%205%2C000%20and%20it%20is%20very%20time%20consuming%20to%20do%20manually.%20I%20need%20to%20find%20a%20way%20to%20change%20the%20cost%20price%20in%20my%20file%20to%20what%20is%20in%20my%20suppliers%20file%20on%20a%20monthly%20basis.%20Another%20issue%20is%20that%20I%20do%20not%20have%20everything%20on%20my%20list%20that%20is%20on%20my%20suppliers%20list%2C%20I%20have%20say%205%2C000%20items%20and%20they%20have%20over%2020%2C000%20items%20so%20I%20can't%20just%20copy%20and%20paste.%20If%20someone%20has%20a%20solution%20it%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22My%20File%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351634iA3DF471D0A9F12E4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MyFile.jpg%22%20alt%3D%22My%20File%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EMy%20File%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Supplier%20File%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351635i3C9CF0F3E4946B3A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SupplierFile.jpg%22%20alt%3D%22Supplier%20File%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ESupplier%20File%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3219424%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219650%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20Excel%20data%20from%20another%20Excel%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219650%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319314%22%20target%3D%22_blank%22%3E%40Whites_Marine%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20use%20vlookup%20function%2C%20that%20will%20help%20you.%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(A3%2C%5BBook2%5DSheet1!%24A%241%3A%24B%2420%2C2%2CFALSE)%2C%22Item%20not%20available%22)%3C%2FP%3E%3CP%3Ewrite%20this%20formula%20in%20G3%20(the%20cost)%2C%20then%20fill%20it%20down%20the%20whole%20table%2C%20it%20will%20bring%20you%20the%20price%20from%20the%20work%20book%20called%20%3CSTRONG%3EBook2%2C%20Sheet1%3C%2FSTRONG%3E%20as%20the%20names%20in%20your%20picture.%3C%2FP%3E%3CP%3EIf%20the%20item%20is%20not%20available%2C%20it%20will%20show%20you%20%22item%20not%20available%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have an Excel file that I use to keep up with pricing, this file is represented by the first attached picture. I also get a file from my supplier every month that gives me the updated cost of items for the month. This file is represented by the second attached picture. I have been going through and manually changing the cost price, but lately the number of items on my list has grown to over 5,000 and it is very time consuming to do manually. I need to find a way to change the cost price in my file to what is in my suppliers file on a monthly basis. Another issue is that I do not have everything on my list that is on my suppliers list, I have say 5,000 items and they have over 20,000 items so I can't just copy and paste. If someone has a solution it would be greatly appreciated.

 

My FileMy FileSupplier FileSupplier File

3 Replies

Hi @Whites_Marine 

 

You would use vlookup function, that will help you.

=IFERROR(VLOOKUP(A3,[Book2]Sheet1!$A$1:$B$20,2,FALSE),"Item not available")

write this formula in G3 (the cost), then fill it down the whole table, it will bring you the price from the work book called Book2, Sheet1 as the names in your picture.

If the item is not available, it will show you "item not available"

@Jihad Al-Jarady 

 

Thanks, it works perfectly.

If it helps you, would you please click on mark as best response to close the topic