Forum Discussion
Solved
Help with formula to pull inventory #s from one excel sheet and input into another
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.
=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.
=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)
