VLOOKUP for 1000 inventories items

%3CLINGO-SUB%20id%3D%22lingo-sub-1844521%22%20slang%3D%22en-US%22%3EVLOOKUP%20for%201000%20inventories%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844521%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20inventory%20column%20with%201000%20items%2C%20each%20in%20its%20own%20row%20in%20a%20worksheet.%26nbsp%3B%20Each%20item%20has%20its%20own%20price%20in%20a%20different%20column%20using%20the%20same%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20use%20VLOOKUP%20that%20when%20an%20inventoried%20item%20is%20selected%20from%20my%20main%20worksheet%2C%20it%20selects%20the%20right%20price%20of%20that%20item%20and%20puts%20that%20price%20in%20the%20right%20column%20on%20my%20main%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20can%20use%20the%20VLOOKUP%20function%20for%20each%20of%201000%20items%2C%20but%20this%20would%20be%20very%20time%20consuming.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20use%20the%20VLOOKUP%20function%20automatically%20for%20each%20of%20the%201000%20rows%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1844521%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846354%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20for%201000%20inventories%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854520%22%20target%3D%22_blank%22%3E%40Gary_Naskrent%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20sounds%20as%20if%20you%20don't%20fully%20understand%20how%20VLOOKUP%20works.%20If%20on%20your%20%22main%20worksheet%22%20you%20are%20entering%20a%20Product%20Code%20(or%20some%20other%20identifier)%20in%2C%20say%2C%20sell%20A2%2C%20you%20should%20be%20able%20to%20put%20VLOOKUP%20in%20to%20cell%20B2%20and%20have%20it%20search%20through%20all%201%2C000%20rows%20of%20the%20inventory%20list%20in%20the%20other%20worksheet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20also%20might%20be%20helpful%20if%20you%20research%20using%20XLOOKUP%2C%20a%20newer%2C%20more%20powerful%20and%20flexible%20function.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20absence%20of%20seeing%20your%20actual%20workbook%2C%20let%20me%20point%20you%20to%20a%20web%20resource%20that%20can%20help%20you%20with%20how%20these%20functions%20are%20best%20deployed.%20If%20you%20still%20are%20having%20troubles%2C%20please%20come%20back%20and%20post%20either%20your%20actual%20workbook%20or%20a%20good%20facsimile%20of%20it%20(in%20Excel%2C%20not%20an%20image).%3C%2FP%3E%0A%3CP%3EVLOOKUP%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dvlookup%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dvlookup%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EXLOOKUP%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dxlookup%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fsearch%3Fquery%3Dxlookup%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846847%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20for%201000%20inventories%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846847%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESounds%20like%20you%20may%20need%20to%20work%20with%20absolute%20references%2C%20i.e.%20fixing%20cells%20%2F%20ranges.%26nbsp%3B%20If%20I%20interpret%20correctly%2C%20you%20need%20a%20vlookup%20to%20work%20in%20a%20number%20of%20rows%20but%20referencing%20the%20same%20inventory%20list.%26nbsp%3B%20If%20so%2C%20you%20just%20need%20to%20fix%20the%20range%20for%20the%20list%20in%20the%20formula%20with%20dollar%20signs%20(%24)%20so%20that%20it%20doesn't%20'shift'%20when%20copying%20down%20into%20the%20below%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3DVLOOKUP(%24A2%2CSheet2!%24A%241%3A%24B%241000%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCopied%20down%2C%20formula%20in%20next%20row%20would%20read%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3DVLOOKUP(%24A3%2CSheet2!%24A%241%3A%24B%241000%2C2%2CFALSE)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I have an inventory column with 1000 items, each in its own row in a worksheet.  Each item has its own price in a different column using the same worksheet.

 

I want to use VLOOKUP that when an inventoried item is selected from my main worksheet, it selects the right price of that item and puts that price in the right column on my main worksheet.

 

I know I can use the VLOOKUP function for each of 1000 items, but this would be very time consuming.

 

How can I use the VLOOKUP function automatically for each of the 1000 rows?

 

2 Replies
Highlighted

@Gary_Naskrent 

 

It sounds as if you don't fully understand how VLOOKUP works. If on your "main worksheet" you are entering a Product Code (or some other identifier) in, say, sell A2, you should be able to put VLOOKUP in to cell B2 and have it search through all 1,000 rows of the inventory list in the other worksheet.

 

It also might be helpful if you research using XLOOKUP, a newer, more powerful and flexible function.

 

In the absence of seeing your actual workbook, let me point you to a web resource that can help you with how these functions are best deployed. If you still are having troubles, please come back and post either your actual workbook or a good facsimile of it (in Excel, not an image).

VLOOKUP  https://exceljet.net/search?query=vlookup

 

XLOOKUP  https://exceljet.net/search?query=xlookup

 

Highlighted

@Gary_Naskrent 

Sounds like you may need to work with absolute references, i.e. fixing cells / ranges.  If I interpret correctly, you need a vlookup to work in a number of rows but referencing the same inventory list.  If so, you just need to fix the range for the list in the formula with dollar signs ($) so that it doesn't 'shift' when copying down into the below rows: 

 

Example:     =VLOOKUP($A2,Sheet2!$A$1:$B$1000,2,FALSE)

                   Copied down, formula in next row would read:

                   =VLOOKUP($A3,Sheet2!$A$1:$B$1000,2,FALSE)