Need to identify if a product in the sales invoice is in the contracted list and produce the same result that is on that list i.e. TRUE or FALSE

[McTam]

Excel 2007
	A	B	C	D	E	F	G
1	Contracted	Product code	Item Description		Contracted	Product code	Product Description
2	TRUE	bwA4	A4 BW Copy/ Print		FALSE	cSCANA0	A0 Colour Scan to File
3	TRUE	bwA3DS	A3 DS BW Copy/ Print		FALSE	cSCANA1	A1 Colour Scan to File
4	TRUE	cA3DS	A3 Double Sided Colour Copy/ Print		FALSE	cSCANA2	A2 Colour Scan to File
5	FALSE	cA3	A3 Colour Copy/ Print		FALSE	cSCANA3	A3 Colour Scan to File
6	TRUE	CB	Comb Bind		FALSE	cSCANA4	A4 Colour Scan to File
7	FALSE	cA3	A3 Colour Copy/ Print		FALSE	cSRA3	SRA3 Colour Copy/ Print
8	TRUE	CB	Comb Bind		FALSE	DVDB	DVD Burning
9	FALSE	LAB15x1	Labour Charge (15min x1)		FALSE	ENCAPA0	A0 Encapsulation
10	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	ENCAPA1	A1 Encapsulation
11	TRUE	cA4	A4 Colour Copy/ Print		FALSE	ENCAPA2	A2 Encapsulation
12	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	PERF	Perfect Bind
13	TRUE	cA4	A4 Colour Copy/ Print		FALSE	xFILE	4D Ring File
14	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		TRUE	bwA3	A3 BW Copy/ Print
15	TRUE	cA4	A4 Colour Copy/ Print		TRUE	bwA3DS	A3 DS BW Copy/ Print
16	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		TRUE	bwA4	A4 BW Copy/ Print
17	TRUE	cA4	A4 Colour Copy/ Print		TRUE	bwA4DS	A4 BW Double Sided Copy/ Print
18	TRUE	cA3	A3 Colour Copy/ Print		TRUE	cA3	A3 Colour Copy/ Print
19	TRUE	CB	Comb Bind		TRUE	cA3DS	A3 Double Sided Colour Copy/ Print
20	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		TRUE	cA4	A4 Colour Copy/ Print
21	TRUE	cA4DS	A4 Double Sided Colour Copy/ Print		TRUE	cA4DS	A4 Double Sided Colour Copy/ Print
22	FALSE	cA3	A3 Colour Copy/ Print		TRUE	ENCAPA3	A3 Encapsulation
23	TRUE	CB	Comb Bind		TRUE	ENCAPA4	A4 Encapsulation
24	FALSE	cA3	A3 Colour Copy/ Print		TRUE	ENCAPA5	A5 Encapsulation
25	TRUE	CB	Comb Bind		TRUE	ENCAPA6	A6 Encapsulation
26	FALSE	bwA4DS	A4 BW Double Sided Copy/ Print		TRUE	LAB15x1	Labour Charge (15min x1)
27	TRUE	xFILE	4D Ring File		TRUE	LAB15x1.5	Labour Charge (15min x1.5)
28	TRUE	cA3	A3 Colour Copy/ Print		TRUE	LAB15x2	Labour Charge (15min x2)
29	TRUE	cA4	A4 Colour Copy/ Print		TRUE	pA3100	A3 100gsm Paper
30	FALSE	pA4CARD	A4 Heavyweight Paper/ Card		TRUE	pA3CARD	A3 Heavyweight Paper/ Card
31	TRUE	cA4	A4 Colour Copy/ Print		TRUE	pA3TEAR	A3 Tear Proof Paper
32	TRUE	cA3	A3 Colour Copy/ Print		FALSE	pA3TINT	A3 Tinted Paper
33	FALSE	CB	Comb Bind
34
35		Sheet One	Invoice Detail			Sheet Two	Product List
36
37
Both Sheets

I did post this before but didn't get any responses which I must admit is very unusual. Hope the html will work? As I mention above I generate an Invoice which has various items on. Some of these are in the agreed contract and some outside of it. From the list of products on the Product List I need to identify on the Invoice Detail sheet if they are TRUE or FALSE looking at the values in Product List sheet. I hope this makes sense. I have tried using VLOOKUP but I think it needs to look at too many variables?

thank you in advance Alex

 

[Andrew Poulsom]

Maybe try:

=INDEX(E$2:E$32,MATCH(1,INDEX((F$2:F$32=B2)*(G$2:G$32=C2),),FALSE))

 

[McTam]

Hi thanks, that doesn't really work. I probably didn't explain myself properly, I need Column A Sheet 1 to show the corresponding value in Sheet Two. Sheet 1 is the Invoice and sheet 2 is the stock list with items in the contract and out of the contract (TRUE or FALSE). Column E,F,and G are actually A,B, and C in sheet 2. So it will look at the Product Description and whether that product is in contract or not (TRUE or FALSE) and return that in the cell in Column A Sheet 1.

Clear as mud I know but can't really explain it any other way??

thanks again Alex

 

[Rick Rothstein]

Hi thanks, that doesn't really work. I probably didn't explain myself properly, I need Column A Sheet 1 to show the corresponding value in Sheet Two. Sheet 1 is the Invoice and sheet 2 is the stock list with items in the contract and out of the contract (TRUE or FALSE). Column E,F,and G are actually A,B, and C in sheet 2. So it will look at the Product Description and whether that product is in contract or not (TRUE or FALSE) and return that in the cell in Column A Sheet 1.

So, adjusting Andrew's formula so that Column E references are changed to Column A references on Sheet2 (and so on for Column F and G), this formula results...

=INDEX(Sheet2!A$2:A$32,MATCH(1,INDEX((Sheet2!B$2:B$32=B2)*(Sheet2!C$2:C$32=C2),),FALSE))

Does it work for you?

 

[McTam]

No sorry it doesn't I have made another copy of the sheet and hopefully this show better what I am looking for,

Excel 2007
	A	B	C	D	E	F	G
1	Column A Invoice Detail	Column L Invoice Detail	Column M Invoice Detail		Column A Product List	Column B Product List	Column C Product List
2	Contracted	Product code	Item Description		Contracted	Product code	Product Description
3	TRUE	bwA4	A4 BW Copy/ Print		FALSE	bwA0	A0 BW Copy/ Print
4	TRUE	bwA3DS	A3 DS BW Copy/ Print		FALSE	bwA1	A1 BW Copy/ Print
5	TRUE	cA3DS	A3 Double Sided Colour Copy/ Print		FALSE	bwA2	A2 BW Copy/ Print
6	FALSE	cA3	A3 Colour Copy/ Print		FALSE	bwSCANA0	A0 Black & White Scan to File
7	TRUE	CB	Comb Bind		FALSE	bwSCANA1	A1 Black & White Scan to File
8	FALSE	cA3	A3 Colour Copy/ Print		FALSE	bwSCANA2	A2 Black & White Scan to File
9	TRUE	CB	Comb Bind		FALSE	bwSCANA3	A3 Black & White Scan to File
10	FALSE	LAB15x1	Labour Charge (15min x1)		FALSE	bwSCANA4	A4 Black & White Scan to File
11	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	CB	Comb Bind
12	TRUE	cA4	A4 Colour Copy/ Print		FALSE	CDB	CD Burn
13	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	cGA0	A0 Colour Plot - Graphics
14	TRUE	cA4	A4 Colour Copy/ Print		FALSE	cGA1	A1 Colour Plot - Graphics
15	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	cGA2	A2 Colour Plot - Graphics
16	TRUE	cA4	A4 Colour Copy/ Print		FALSE	cLA0	A0 Colour Plot - Line
17	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	cLA1	A1 Colour Plot - Line
18	TRUE	cA4	A4 Colour Copy/ Print		FALSE	cLA2	A2 Colour Plot - Line
19	TRUE	cA3	A3 Colour Copy/ Print		FALSE	cPA0	A0 Colour Print - Photo Quality
20	TRUE	CB	Comb Bind		FALSE	cPA1	A1 Colour Print - Photo Quality
21	TRUE	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	cPA2	A2 Colour Print - Photo Quality
22	TRUE	cA4DS	A4 Double Sided Colour Copy/ Print		FALSE	cSCANA0	A0 Colour Scan to File
23	FALSE	cA3	A3 Colour Copy/ Print		FALSE	cSCANA1	A1 Colour Scan to File
24	TRUE	CB	Comb Bind		FALSE	cSCANA2	A2 Colour Scan to File
25	FALSE	cA3	A3 Colour Copy/ Print		FALSE	cSCANA3	A3 Colour Scan to File
26	TRUE	CB	Comb Bind		FALSE	cSCANA4	A4 Colour Scan to File
27	FALSE	bwA4DS	A4 BW Double Sided Copy/ Print		FALSE	cSRA3	SRA3 Colour Copy/ Print
28	TRUE	xFILE	4D Ring File		FALSE	DVDB	DVD Burning
29
30		Invoice Detail				Product List
31
32
33
Both Sheets

 

[Andrew Poulsom]

Please explain the result of TRUE in A3.

 

[Rick Rothstein]

Columns L and M... you were expecting us to guess that from what you posted originally? Really???

See if this formula does what you want...

=INDEX(Sheet2!A$2:A$32,MATCH(1,INDEX((Sheet2!B$2:B$32=L2)*(Sheet2!C$2:C$32=M2),),FALSE))

 

[McTam]

It is looking at the item in the list of products. So the Invoice will have sales entries, the items in columns L and M these need to be looked up in Product List and return the value TRUE or FALSE from the product list. Hope this explains?

Alex

 

[McTam]

Should have mentioned I have only taken a snapshot for the HTML so there are a lot more entries which is why they don't fully coordinate

 

[McTam]

I thought I would be able to alter sheet names and column references. Apologies