Index/Match Paired with IF Function?!??

[aware073]

I need to formulate something that will look up by part number (Columns A) to put an X under the Vendor names in Sheet 2 to show which of the part numbers we've previously purchased from each vendor (column L in sheet 1). I know index match will look up by two values (part number and vendor code?) to insert a value but I cant seem to get that to work here.

**Plus, more times than not I have part numbers duplicated in the list on sheet 1 because we've purchased from multiple vendors.

I was thinking if it would just return the vendor number I could put that formula inside an IF formula for >0,X,"") to just get an X in the box for each vendor we've purchased the item from before.
Any suggestions? I'm stumped and I know this will be an easy one for you guys.
Thanks in advance!!



Sheet 1

Excel 2010
	A	J	L	M
1	Material	LPP	Vendor Code	Vendor Name
2	20000001
4	20000002
6	20000003
8	20000004
9	20000004
11	20000005
13	20000006	$ 7.02	300390	JOY GLOBAL UNDERGOUND MINING LLC
14	20000006
16	20000007	$ 10.22	300390	JOY GLOBAL UNDERGOUND MINING LLC
17	20000007	$ 10.42	300390	JOY GLOBAL UNDERGOUND MINING LLC
18	20000007	$ 9.92	300390	JOY GLOBAL UNDERGOUND MINING LLC
19	20000007
21	20000008	$ 13.18	300242	FAIRMONT SUPPLY COMPANY
22	20000008
24	20000009	$ 2.07	305412	GAULEY-ROBERTSON INC
FE Inv with LPP

Sheet 2

Excel 2010
	A	F	G	H	I	J	K	L
1			300003	300013	300020	300026	300030	300047
2	Material No.	Lowest LPP	ABSOLUTE INNOVATIONS	ASSOCIATED PALLET INC	BAKER&SON'S PLUMBING INC	BATTERY SPECIALISTS	BELT TECH INDUSTRIAL INC	BOBCAT OF SPRINGFIELD
3	20000001
4	20000002
5	20000003
6	20000004
7	20000005
8	20000006
9	20000007	$ 9.92
10	20000008
11	20000009	$ 2.07
12	20000010
Material & Vendors

 

[MarcelBeug]

Just out of my head, enter in G3, copy to the right and below, the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):

=IF(ISERROR(MATCH($A3&G$1,'Sheet 1'$A$2:$A$24&'Sheet 1'!$L$2:$L$24,0),"","X")

 

[aware073]

Just out of my head, enter in G3, copy to the right and below, the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER):

=IF(ISERROR(MATCH($A3&G$1,'Sheet 1'$A$2:$A$24&'Sheet 1'!$L$2:$L$24,0),"","X")

Thanks so much for your reply!! I'm getting "There are too many arguments for this function" now.. Any ideas??

 

[Stridhan]

perhaps this in sheet2 cell G3

=if(sumproduct(--('FE Inv with LPP'!$B$2:$B$16=$B3)*--('FE Inv with LPP'!$D$2:$D$16=D$1))>0,"X","")

 

[aware073]

In the first formula you gave me, every time I enter it and either hit enter or ctrl-shift-enter, Documents Library opens up.. No clue how that's happening.
This is what the second formula is giving me. I cant figure out what condition its bringing the X in on but its not right

Excel 2010
	A	F	G	H	I	J
1			300003	300013	300020	300026
2	Material No.	Lowest LPP	ABSOLUTE INNOVATIONS	ASSOCIATED PALLET INC	BAKER&SON'S PLUMBING INC	BATTERY SPECIALISTS
3	20000001
4	20000002
5	20000003
6	20000004
7	20000005
8	20000006
9	20000007	$ 9.92
10	20000008		X
11	20000009	$ 2.07
12	20000010
13	20000011		X
14	20000012		X
15	20000013		X
16	20000014		X
17	20000015	$ 38.95
18	20000016		X
19	20000017		X
20	20000018
21	20000019
22	20000020		X
23	20000021		X
24	20000022		X
Material & Vendors

 

[Stridhan]

ops, my test had different columns, try

=if(sumproduct(--('FE Inv with LPP'!$A$2:$A$16=$A3)*--('FE Inv with LPP'!$L$2:$L$16=G$1))>0,"X","")

 

[MarcelBeug]

Thanks so much for your reply!! I'm getting "There are too many arguments for this function" now.. Any ideas??

A "!" was missing (the one in bold/red below). Apologies.

=IF(ISERROR(MATCH($A3&G$1,'Sheet 1'[COLOR="#FF0000"][B]![/B][/COLOR]$A$2:$A$24&'Sheet 1'!$L$2:$L$24,0),"","X")

 

[aware073]

ops, my test had different columns, try

=if(sumproduct(--('FE Inv with LPP'!$A$2:$A$16=$A3)*--('FE Inv with LPP'!$L$2:$L$16=G$1))>0,"X","")

Do I need to change anything if my spreadsheet is 40,000 lines.. Its working when I put it in for Joy, who has multiple products right at the top of the list but for the Absolute Innovations vendor I'm getting nothing down around row 9700.

 

[aware073]

Nevermind.. Got it! Thank you guys so much.. Cant tell you how much I appreciate it.

 

[Stridhan]

you're welcome