Filter and Index match range if some condition match

[Vishaal]

HI All,

Thanks in advance,

we have two sheet and want some result

First Sheet

Example.xlsx
	A	B	C
1	O No	Date	desc
2	658595	21-07-2015	o is still pending
3	3596246	21-07-2015	o is sent to us
4	658947	10-07-2015	o is pending due to stock
5	625478	08-07-2015	O is in dispatch process
Sheet1

Second Sheet

Example.xlsx
	A	B	C	D
1	I Date	Status	Track	Comment
2	21-07-2015	on rent	359624/A5	recd very good
3	28-07-2015	booked	3596246/A68	recd order again
4	10-07-2015	update soon	658595/65	best of luck
5	05-07-2015	housefull	359624/154A	great product
6	05-07-2015	housefull	359624/154A	order again
Sheet2

We want the following

(1) if Sheet1!A2 is matched with Sheet2!C2:C6 then copy the entire row value to Sheet1! From E (in Sheet2!C3:C6, value will match if we get the Data/Value before the symbol "/" )
(2) want to get only data which is not match as per point 1 (in the above sheets Sheet2!A6:D6 data is not matched with sheet1!A2:A5, so we need this data in sheet3)

Help pls

 

[CA_Punit]

How about this

Book1
	A	B	C	D	E	F	G	H
1	O No	Date	desc
2	658595	21/07/2015	o is still pending	42195	update soon	658595/65	best of luck
3	3596246	21/07/2015	o is sent to us	42213	booked	3596246/A68	recd order again
4	658947	10/07/2015	o is pending due to stock	NA	NA	NA	NA
5	625478	08/07/2015	O is in dispatch process	NA	NA	NA	NA
6
7	I Date	Status	Track	Comment
8	21/07/2015	on rent	359624/A5	recd very good
9	28/07/2015	booked	3596246/A68	recd order again
10	10/07/2015	update soon	658595/65	best of luck
11	05/07/2015	housefull	359624/154A	great product
12	05/07/2015	housefull	359624/154A	order again
13
14
15
16	Sheet 3
17
18	42206	on rent	359624/A5	recd very good
19	42190	housefull	359624/154A	great product
20	42195	update soon	658595/65	best of luck
21
22
Sheet1

Cell Formulas
Range	Formula
D2:G5	D2	=IFERROR(INDEX($A$8:$D$12,LARGE(IFERROR(SEARCH(A2,$C$8:$C$12)^0*ROW($A$1:$A$5),FALSE),1),{1,2,3,4}),"NA")
A18:D20	A18	=INDEX($A$8:$D$12,AGGREGATE(15,6,1/1/NOT(ISNUMBER(MATCH(C8:C12,F2:F5)))*ROW($A$1:$A$5),ROWS($A$1:A1)),{1,2,3,4})
Dynamic array formulas.

 

[Vishaal]

not working, just copy and paste your answer, find the below

Book1
	A	B	C	D	E	F
1	O No	Date	desc
2	658595	21-07-2015	o is still pending	NA
3	3596246	21-07-2015	o is sent to us	NA
4	658947	10-07-2015	o is pending due to stock	NA
5	625478	08-07-2015	O is in dispatch process	NA
6
7	I Date	Status	Track	Comment
8	21-07-2015	on rent	359624/A5	recd very good
9	28-07-2015	booked	3596246/A68	recd order again
10	10-07-2015	update soon	658595/65	best of luck
11	05-07-2015	housefull	359624/154A	great product
12	05-07-2015	housefull	359624/154A	order again
13
14
15
16	Sheet 3
17
18	42206
19	42213
20	42195
Sheet1

help pls

 

[Vishaal]

using ms excel 2010

 

[CA_Punit]

Use Ctrl+shift+enter and not just enter

 

[Vishaal]

its working Punitji,

thanks