Help with formula to pull inventory #s from one excel sheet and input into another

Copper Contributor

Hi there,


Could I get some formula help please? I have 1 inventory sheet that I need to update with the #s from another sheet. 

What I would like to accomplish is:


If an item # in WORKSHEET 1 (column b) is in WORKSHEET 2 (column b), then take the corresponding Net Stock from WORKSHEET 2 (Column F) for that item number and input it into WORKSHEET 1 Column C (Qty On Hand).


Basically, I want to automate updating the inventory sheet. 


I've attached samples of what my worksheets look like. 

7 Replies
best response confirmed by AL789 (Copper Contributor)


=VLOOKUP(B2,'[WORKSHEET 2.xlsx]Sheet1'!$B$2:$F$30,5,FALSE)

This formula returns the intended result if the ItemNo in column B in WORKSHEET 2 are formatted as in WORKSHEET 1.


@OliverScheurich  Thank you. Is there a way to modify the formula so that it also looks for matches within other text? For example, say I'm looking for 1900-1, but on the lookup sheet it is listed as ET1900-1JJ in the cell. Can I have the formula look up 1900-1 within this cell (I.e. ignore the ET and the JJ) and then pull the quantity? 

=VLOOKUP("*" & B2 & "*",'[WORKSHEET 2.xlsx]Sheet1'!$B$2:$F$30,5,FALSE)
Thank you. This works for the example in my reply above but doesn't work for the standard numbers. For these, I get the #NA error. Is there a solution that captures the information for both? For example: 26803, 26804, etc.?
=VLOOKUP("*" & TEXT(B2,"@") & "*",'[WORKSHEET 2.xlsx]Sheet1'!$B$2:$F$30 & "",5,FALSE)
Hmm I can't seem to get this to work. I'm getting #NA for all now using this formula. Thoughts?