Microsoft Excel- Search for Multiple Items at Once?

So you've got two lists? 1 has the list of all inventory. 1 has a list of numbers for you. You need to find if each of the numbers on your list are on the master list. Is that gist of your problem?

 

Make a list of the numbers you want. To the right, make a vlookup formula:

=VLookup($A#,[table you're referencing],[columns over to the data you want from the inventory number],false)

Am I missing something here?

 

You can use the countif function.

=COUNTIF(RANGE1, Criteria)

Range1 would be the master list with inventory numbers. Then use each inventory number of the list handed to you as the criteria.

If you give specific columns and rows, I can give you a quick formula.

 

If the info handed to you is on 3 sheets, you'll want to format the inventory numbers so they look the same as the master list (leading zeroes, dashes, etc.). Let's say they are in column B, cells 1-1000

Let's say the master list is column A rows 2 - 23000.

After you've formatted the list you received, go to column C and type "=COUNTIF(" then highlight the master list A2-A23000. Excel should put in the file name and worksheet name as well as anchor ($) the cell numbers. Type a comma then point to the first cell of your sheets.

You should get a formula that looks like this: =COUNTIF('[MASTER INVENTORY FILE.xls]Master'!$A$23:$A$23000,B1).

Copy that down in Column C.

That will tell you if there's a corresponding entry in the Master Inventory List.

If you want to get corresponding information, you'll need to use the Lookup function.

IMPORTANT: Make sure your Master Inventory List is sorted by Inventory Number. If you lookup in an unsorted list, you get garbage results unless you're using a sumif or countif function.

In Column D, use the following formula: =IF(C1=0,"",LOOKUP(B1,'[MASTER INVENTORY FILE.xls]Master'!$A$23:$A$23000, '[MASTER INVENTORY FILE.xls]Master'!$C$23:$C$23000))

That's saying if the count is zero, there's nothing to lookup so leave the value blank. If there is a corresponding value, match the inventory number in column A and find the corresponding value in column C.

That's kind of generic help. If you have a more detailed question on what you're trying to compare\compile, I can give you a better fit formula.

 

if the inventory numbers don't vary based on revisions and the like, I would suggest two things.

as an earlier person suggested, a vlookup would be best to identify which items are on your list of responsibilties.

secondly, once you have defined those you are responsible for a pivot table might be best to summarize the rack up of what is and isn't on your list.

just make sure your columns are clearly defined for the pivot table