Multiple Worksheets

G

gipsynic

Hi there, I am only a basic user of Excel so am hoping someone can help me
with my question as it will save me so much time.
In my workbook I have 2 work sheets.
Sheet 1 is my Location List for our stock we store in our store room
Column A is my Store Room Location
Column B is our Stock Number
Sheet 2 is our Items list exported from our software programme
Column V is headed up CUSTFLD1 but it contains our stock Number
Column Y is headed up CUSTFLD4-and is where I need the Store Room Location
from Sheet 1 to go.

My problem is not all of the stock numbers have a store room location. I
have been manually cutting & pasting which is taking me forever & I keep
getting it all mixed up. Is there someway Excel can match the Stock Number
from Sheet 1 to Sheet 2 & Enter the corresponding Store Room Location from
Sheet 1 to Sheet 2?
Oh, I hope this makes sense to somebody out there & I would really love to
hear from someone. Thanks
 
M

Max

One way is index/match

In Sheet2,
In Y2: =INDEX(Sheet1!A:A,MATCH(V2,Sheet1!B:B,0))
Copy down. This will return #N/As for unmatched cases

If you need an error trap to return blanks: "" instead of ugly #N/As,
put in Y2:
=IF(ISNA(MATCH(V2,Sheet1!B:B,0)),"",INDEX(Sheet1!A:A,MATCH(V2,Sheet1!B:B,0)))
Copy down

voila? click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top