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

 

[McTam]

Sorry still doesn't work for me. I'll press on and see if I can get it to work,

thanks Alex

 

[Andrew Poulsom]

Why don't you post the data you are using where my formula doesn't work?

 

[McTam]

There is a lot of commercial information in there regard the police so I can't do that I'm afraid. Thank you anyway,

Alex

 

[Andrew Poulsom]

The sample data in your original post doesn't look particularly confidential. Are you working on something else now?

 

[McTam]

I had to remove all the confidential info hence the columns were out of sync.

Alex

 

[Andrew Poulsom]

So why don't you share the workbook with the confidential data removed?

 

[McTam]

I have tried to upload the whole workbook but it say that I have exceeded the HTML limit,

Alex

 

[Andrew Poulsom]

Can you put it on a share like Box.com and post the URL?

 

[McTam]

Here is the link but without sharing I don't think you will be able to see it,
https://www.dropbox.com/home/Excel Sheet
Alex

 

[Rick Rothstein]

Here is the link but without sharing I don't think you will be able to see it,
https://www.dropbox.com/home/Excel Sheet

I have a Drop Box account, but I am not totally sure how to use it (because I have not used it yet), but I notice a folder named Public when I log in... perhaps right-clicking the file inside of Drop Box and selecting "Share link..." will make it visible to others?