how to know wich Invoices exist in a range, based on a range

[jamiguel77]

hi in cell m2, how to filter or bdextact or bdfilter

how to know the invoices that exist on column F based on column A? and same in Q2, but that not exist.

Thanks

Libro2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Master	Vendor			Detalle							Exist	Master	Vendor			No Exist	Master	Vendor
2	AF454	HKB			1	AF454	415	434.056761	180133.556
3	FD432	Coca Cola			2	FD432	189	47.9131886	9055.59265
4	LB342	PepsiCola			3	FD432	423	252.420701	106773.957
5	RD432	Walmart			4	AF454	635	560.350584	355822.621
6	V6742	Sams			5	V6742	638	582.30384	371509.85
7	B5332	Soriana			6	AF454	415	77.6293823	32216.1937
8					7	FD432	661	623.706177	412269.783
9					8	V6742	933	103.088481	96181.5526
10					9	FD432	123	108.430718	13336.9783
11					10	AF454	736	359.933222	264910.851
12					11	FD432	837	446.24374	373506.01
13					12	FD432	883	326.210351	288043.74
14					13	V6742	110	204.507513	22495.8264
15					14	FD432	253	40.5676127	10263.606
16					15	FD432	679	461.435726	313314.858
17					16	V6742	833	313.772955	261372.871
18					17	V6742	683	428.297162	292526.962
19					18	FD432	184	88.0634391	16203.6728
20					19	AT9000	623	386.811352	240983.472
21					20	FD432	742	95.2420701	70669.616
22					21	HY8900	183	483.806344	88536.5609
23					22	AT9000	342	99.1652755	33914.5242
24					23	HY8900	549	101.419032	55679.0484
25					24	AT9000	706	479.131886	338267.112
26					25	FD432	275	241.318865	66362.6878
27					26	HY8900	795	295.659432	235049.249
28					27	FD432	202	133.973289	27062.6043
29					28	V6742	206	264.106845	54406.01
30					29	AF454	794	484.390651	384606.177
31					30	AF454	233	88.6477462	20654.9249
32					31	FD432	329	147.829716	48635.9766
33					32	V6742	688	132.721202	91312.187
34					33	AF454	336	164.941569	55420.3673
35					34	V6742	761	332.220367	252819.699
Hoja1

 

[AhoyNC]

Like this?
Drag formulas down rows as needed.
Formula will not work in Excel 2007 (no AGGREGATE function in 2007).

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Master	Vendor			Detalle							Exist	Master	Vendor			No Exist	Master	Vendor
2	AF454	HKB			1	AF454	415	434.0568	180133.6			AF454	AF454	HKB			LB342	LB342	PepsiCola
3	FD432	Coca Cola			2	FD432	189	47.91319	9055.593			FD432	FD432	Coca Cola			RD432	RD432	Walmart
4	LB342	PepsiCola			3	FD432	423	252.4207	106774			V6742	V6742	Sams			B5332	B5332	Soriana
5	RD432	Walmart			4	AF454	635	560.3506	355822.6
6	V6742	Sams			5	V6742	638	582.3038	371509.8
7	B5332	Soriana			6	AF454	415	77.62938	32216.19
8					7	FD432	661	623.7062	412269.8
9					8	V6742	933	103.0885	96181.55
10					9	FD432	123	108.4307	13336.98
11					10	AF454	736	359.9332	264910.9
12					11	FD432	837	446.2437	373506
13					12	FD432	883	326.2104	288043.7
14					13	V6742	110	204.5075	22495.83
15					14	FD432	253	40.56761	10263.61
16					15	FD432	679	461.4357	313314.9
17					16	V6742	833	313.773	261372.9
18					17	V6742	683	428.2972	292527
19					18	FD432	184	88.06344	16203.67
20					19	AT9000	623	386.8114	240983.5
21					20	FD432	742	95.24207	70669.62
22					21	HY8900	183	483.8063	88536.56
23					22	AT9000	342	99.16528	33914.52
24					23	HY8900	549	101.419	55679.05
25					24	AT9000	706	479.1319	338267.1
26					25	FD432	275	241.3189	66362.69
27					26	HY8900	795	295.6594	235049.2
28					27	FD432	202	133.9733	27062.6
29					28	V6742	206	264.1068	54406.01
30					29	AF454	794	484.3907	384606.2
31					30	AF454	233	88.64775	20654.92
32					31	FD432	329	147.8297	48635.98
33					32	V6742	688	132.7212	91312.19
34					33	AF454	336	164.9416	55420.37
35					34	V6742	761	332.2204	252819.7
Sheet1

Cell Formulas
Range	Formula
M2:N4	M2	=INDEX($A$2:$B$7,MATCH($L2,$A$2:$A$7,0),{1,2})
R2:S4	R2	=INDEX($A$2:$B$7,MATCH(Q2,$A$2:$A$7,0),{1,2})
Q2:Q5	Q2	=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$2)+1)/ISNA(MATCH($A$2:$A$7,$F$2:$F$35,0)),ROWS($Q$1:Q1))),"")
L2:L6	L2	=IFERROR(INDEX($F$2:$F$35,AGGREGATE(15,6,(MATCH($A$2:$A$7,$F$2:$F$35,0)),ROWS($L$2:L2))),"")
Dynamic array formulas.

 

[SanjayGMusafir]

I drew idea from @DanteAmor reply on this post -

Sum values but exclude if there are duplicate values in a different columns

If there are duplicate values in "Column A" I only want to sum the cost where Column B has "Final" In the example below I only want to sum the Cost values that are in yellow text Job # Adjust Cost 71D0001 75%-95% 3,073,284 71D0001 Final 3,097,975 71D0032 75%-95% 933,248 71D0032 Final...

www.mrexcel.com

Check this and revert -

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Master	Vendor			Detalle							Exist	Master	Vendor			No Exist	Master	Vendor
2	AF454	HKB			1	AF454	415	434.0567613	180133.5559				AF454	HKB				LB342	PepsiCola
3	FD432	Coca Cola			2	FD432	189	47.91318865	9055.592654				FD432	Coca Cola				RD432	Walmart
4	LB342	PepsiCola			3	FD432	423	252.4207012	106773.9566				V6742	Sams				B5332	Soriana
5	RD432	Walmart			4	AF454	635	560.3505843	355822.621
6	V6742	Sams			5	V6742	638	582.3038397	371509.8497
7	B5332	Soriana			6	AF454	415	77.6293823	32216.19366
8					7	FD432	661	623.706177	412269.783
9					8	V6742	933	103.0884808	96181.55259
10					9	FD432	123	108.4307179	13336.9783
11					10	AF454	736	359.933222	264910.8514
12					11	FD432	837	446.2437396	373506.01
13					12	FD432	883	326.2103506	288043.7396
14					13	V6742	110	204.5075125	22495.82638
15					14	FD432	253	40.56761269	10263.60601
16					15	FD432	679	461.4357262	313314.8581
17					16	V6742	833	313.7729549	261372.8715
18					17	V6742	683	428.2971619	292526.9616
19					18	FD432	184	88.06343907	16203.67279
20					19	AT9000	623	386.8113523	240983.4725
21					20	FD432	742	95.24207012	70669.61603
22					21	HY8900	183	483.8063439	88536.56093
23					22	AT9000	342	99.16527546	33914.52421
24					23	HY8900	549	101.4190317	55679.04841
25					24	AT9000	706	479.1318865	338267.1119
26					25	FD432	275	241.3188648	66362.68781
27					26	HY8900	795	295.6594324	235049.2487
28					27	FD432	202	133.9732888	27062.60434
29					28	V6742	206	264.1068447	54406.01002
30					29	AF454	794	484.3906511	384606.177
31					30	AF454	233	88.64774624	20654.92487
32					31	FD432	329	147.8297162	48635.97663
33					32	V6742	688	132.721202	91312.18698
34					33	AF454	336	164.9415693	55420.36728
35					34	V6742	761	332.2203673	252819.6995
Sheet1

Cell Formulas
Range	Formula
M2:M4	M2	=LET(a,A2:A7,b,F:F,FILTER(A2:A7,COUNTIF(b,a)>=1))
N2:N4,S2:S4	N2	=XLOOKUP(M2#,$A$2:$A$7,$B$2:$B$7)
R2:R4	R2	=LET(a,A2:A7,b,F:F,FILTER(A2:A7,COUNTIF(b,a)=0))
Dynamic array formulas.

 

[AhoyNC]

@SanjayGMusafir . OP's profile doesn't show Excel 365 or 2021, so he would not have the FILTER or XLOOKUP functions.

 

[SanjayGMusafir]

OP's profile doesn't show Excel 365 or 2021

@AhoyNC I noticed that while reading OP but (s)he has mentioned web platform, so I thought, like most, he might not have upgraded his current Office version and gave him an option.

I believe it's a typical case where OP(s) can understand updating profile and platform is important.