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]

No I'm afraid that doesn't work either. I need to return for instance the value in cell A3,4,5,6,etc from the Invoice detail extracted from (Sheet 2) the Product List. So for A3 which is bwA4 - A4 BW Copy /Print it's needs to look this value up in the Product List and if it exists return the value TURE or FALSE from that range.
I have tried using VLOOKUP but I think there are more variables than one so I can't get it to work.

thanks again for you help

Alex

 

[Andrew Poulsom]

The formula I posted will work. Just adjust the references to suit your scenario. The syntax is:

=INDEX(ProductListContracted,MATCH(1,INDEX((ProductListProductCode=ProductCode)*(ProductListProductDescription=ProductDescription),),FALSE))

 

[McTam]

Sorry I am not totally au fait with Excel just basic Formulas. When you say this is the syntax I am not completely understanding. Do I need to add ' and " and cells ref's to this string?
Apologies for being so useless but this is why I come to you guys,

Alex

 

[Andrew Poulsom]

On what worksheet and in which range is your Product List table (the lookup range)? In which ranges are the lookup values Product Code and Product Description?

 

[McTam]

the selection I used for the HTML is much shorter than the full product list but effectively here the lookup range is Product List E2-G32. This show whether the item is in or out of contract (TRUE or FALSE). The Invoice Detail looks at this range and the cell in column A must reflect the value in the Product List. Hope this is clear enough as it is quite difficult to explain. Basically the client will get the Invoice Detail but the need to show if the item being charged is in contract or not,

thanks again Alex

 

[Andrew Poulsom]

Can't you give range references? What formula are you using and in what way isn't it working?

 

[McTam]

Sorry you've lost me I am not using any formulas this is what I was asking. I thought you could do a VLOOKUP to look at the range with the Contracted, Product Code and Item Description to return a value but I realise it is looking at more than one criteria.

Alex

 

[Andrew Poulsom]

In Post #3 you said "that doesn't really work" in response to my formula in Post #2. In Post #5 you said "No sorry it doesn't" in response to Rick's question in Post #4. In Post #11 you said "No I'm afraid that doesn't work either" in response to Rick's formula in Post #7. So what formula are you using that doesn't work and in what way isn't it working? The formula I gave you in Post #2 works with your sample data in Post #1.

 

[McTam]

I just get #N/A when I paste your formula Post No2 into Sample Post No1 cell A2. The results need to be in Column A (Contracted) Sheet 1 Invoice Detail. So Invoice Detail Cells 2A, 2B, and 2C should equal Product List 18F, 18G and 18H. Our database picks up cells 2B and 2C (Product Code and Item Description) I just add column A (Contracted) and these cells should look at the Product List and match them. Again sorry if this isn't clear it is difficult to explain properly. Thank you for your perseverance,
Alex

 

[Andrew Poulsom]

Here's what I get:

Excel 2010
	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	TRUE	cA3	A3 Colour Copy/ Print		FALSE	cSCANA3	A3 Colour Scan to File
6	#N/A	CB	Comb Bind		FALSE	cSCANA4	A4 Colour Scan to File
7	TRUE	cA3	A3 Colour Copy/ Print		FALSE	cSRA3	SRA3 Colour Copy/ Print
8	#N/A	CB	Comb Bind		FALSE	DVDB	DVD Burning
9	TRUE	LAB15x1	Labour Charge (15min x1)		FALSE	ENCAPA0	A0 Encapsulation
10	#N/A	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	ENCAPA1	A1 Encapsulation
11	TRUE	cA4	A4 Colour Copy/ Print		FALSE	ENCAPA2	A2 Encapsulation
12	#N/A	pA4CARD	A4 Heavyweight Paper/ Card		FALSE	PERF	Perfect Bind
13	TRUE	cA4	A4 Colour Copy/ Print		FALSE	xFILE	4D Ring File
14	#N/A	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	#N/A	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	#N/A	CB	Comb Bind		TRUE	cA3DS	A3 Double Sided Colour Copy/ Print
20	#N/A	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	TRUE	cA3	A3 Colour Copy/ Print		TRUE	ENCAPA3	A3 Encapsulation
23	#N/A	CB	Comb Bind		TRUE	ENCAPA4	A4 Encapsulation
24	TRUE	cA3	A3 Colour Copy/ Print		TRUE	ENCAPA5	A5 Encapsulation
25	#N/A	CB	Comb Bind		TRUE	ENCAPA6	A6 Encapsulation
26	TRUE	bwA4DS	A4 BW Double Sided Copy/ Print		TRUE	LAB15x1	Labour Charge (15min x1)
27	FALSE	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	#N/A	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	#N/A	CB	Comb Bind
Sheet1

Cell Formulas
Range	Formula
A2	=INDEX(E$2:E$32,MATCH(1,INDEX((F$2:F$32=B2)*(G$2:G$32=C2),),FALSE))