VLookup wih an IF value, to return new data?

MrPear

New Member
Joined
Dec 15, 2016
Messages
2
Greetings Wizards.

I'm producing a milestone report were I want to search 2 sheets and bring back any new data in a 3rd.

Essentially, both sheets have the same layout as below. With the detail obviously compiled beneath

CDEFG
Milestone IDMilestone LevelMilestone NameFinish% Complete

<tbody>
</tbody>


So,

Sheet one contains milestones from last week

Sheet two contains milestones from this week

On the third sheet (which is called milestones added) I am intending on the same layout as above and I want to have a lookup which will be searching both of the milestone sheets, and providing data solely on new milestones (new data) that have been added within the week (sheet 2)

I can do a lookup on what's new (what doesn't match) but I can't figure out how to return it correctly.

Your help is very much appreciated.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
IF data in sheet1 and sheet2 are from C2:G6,
ARRAY Formula in C2 of sheet 3 is,
Code:
=IFERROR(INDEX(Sheet2!C$2:C$6,SMALL(IF(ISERROR(MATCH(Sheet2!$C$2:$C$6,Sheet1!$C$2:$C$6,0)),ROW(Sheet2!$C$2:$C$6),""),ROWS(Sheet3!$C$2:$C2))-ROW(Sheet3!$C$2)+1),"")
Then drag the formula across.
ARRAY formula is used
To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top