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

[AL789]

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. 

[#2 · Accepted solution]

@AL789 

=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.

[#3]

@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? 

[#4]

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

[#5]

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.?

[#6]

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

[#7]

Hmm I can't seem to get this to work. I'm getting #NA for all now using this formula. Thoughts?

[#8]

@AL789 

see attached Sheet14

https://b23.tv/kbKyPsm