I am working with a large data file. I essentially have two worksheets, 2017 Actual numbers and 2017 Budget Figures. I need to match the "Name" and "Material #" from the 2017 Actual Number sheet with the "Name" and "Material #" from the 2017 budget numbers and return the 2017 Budget price. I have tried Index and Match but cannot get it to work. Any help?
The actual files have ~5,500 line items.
2017 Actual Sheet
2017 Actual.JPG
2017 Budget Sheet
2017 Budget.JPG
you could try this...
=LOOKUP(2,1/('2017 Budget'!$A$2:$A$5='2017 Actual'!A2)/('2017 Budget'!$B$2:$B$5='2017 Actual'!B2),'2017 Budget'!$C$2:$C$5)
Last edited by Sam Capricci; 09-05-2018 at 08:48 AM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
you could also try below
in E3: =INDEX('2017 Budget'!C:C,MATCH('2017 Actual'!A2,'2017 Budget'!A:A,0))
or
=VLOOKUP(A2,'2017 Budget'!A:D,3,0)
OR, this works too:
In E2, drag down.
HTML Code:
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
If your mat# unique then try also
Formula:
Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks