• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 67
  • Last Modified:

Match function look up in Excel

On the DWG tab I have part numbers on column A.  What I wanted is to match part numbers from the BOM tab and pull in cabinet data from column B and list those in the DWG tab under columns B:E.

See attached and example on the DWG Tab.
C--Users-lfreund-Desktop-EE-Index.xlsx
0
LUIS FREUND
Asked:
LUIS FREUND
  • 2
  • 2
2 Solutions
 
stevericeCommented:
Not sure if this is exactly what you want, but have you tried a pivot table? See attached
C--Users-lfreund-Desktop-EE-Index.xlsx
0
 
LUIS FREUNDAuthor Commented:
Thanks for the response Steve,

Similar.....I would like to have it done on the DWG tab....the data changes and this is the format that it needs to be in.
0
 
stevericeCommented:
Is this it? Just moved the Pivot table to the DWG tab
C--Users-lfreund-Desktop-EE-Index.xlsx
0
 
ShumsDistinguished Expert - 2017Commented:
Hi Luis,

First add an helper column (Col C) in BOM Sheet with below formula:
=IF(B2="BI","CABINET 3",IF(B2="LI","CABINET 1",IF(B2="SI","CABINET 2",IF(B2="CV","CABINET 4"))))

Open in new window

Then in DWG sheet add below Array Formula confirmed with Ctrl+Shift+Enter and drag across and below:
=IFERROR(INDEX(BOM!$B:$B,MATCH(1,($A2=BOM!$A:$A)*(B$1=BOM!$C:$C),0)),"")

Open in new window

Hope this helps.
C--Users-lfreund-Desktop-EE-Index.xlsx
0
 
LUIS FREUNDAuthor Commented:
Thank you gents.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now