# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Author 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
Commented:
Is this it? Just moved the Pivot table to the DWG tab
C--Users-lfreund-Desktop-EE-Index.xlsx
0
Distinguished 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"))))
``````
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)),"")
``````
Hope this helps.
C--Users-lfreund-Desktop-EE-Index.xlsx
0

Experts Exchange Solution brought to you by