Iferror index and match formula in VBA

[A Thayuman]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	QTY		Item Number			Description							Price		Unit		Disc %		Total
2			EL-PL24W0612A			24W LED Panel Light 600x1200mm							$ 30.00
3						#N/A
4						#N/A
5
6
7												The idea of using Index and match in VBA is so that I can overwirte the description when needed without having to worry about deleting the formula.
8	Data Validation Table											Formula is rewirtten when the sheet is opend for use again
9												However when:
10			Item Number		Description
11			EL-PL36W0612A		36W LED Panel Light 600x1200mm							This Code "=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))" inbeds in the cell I choose, it works fine when activated on opening the sheet by VBA: =INDEX($Z$3:$Z$30,MATCH(C2, $W$3:$W$30, 0))
12			EL-PL24W0612A		24W LED Panel Light 600x1200mm							Howerver when Cell "C" has no information the "F2" shows #N/A
13			EL-PL24W0312A		24W LED Panel Light 300x1200mm
14			EL-PL24W0606A		24W LED Panel Light 600x600mm							This formula pasted as a code in VBA does not work at all "=IFERROR(=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))," ")"
15			EL-PL12W0306A		12W LED Panel Light 300x600mm							And a message window pops up with "Complie Error, Expected: End Statement
16
17
18
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($E$11:$E15,MATCH(C2, $C$11:$C$15, 0))
M3:M4	M3	=IFERROR(INDEX($AA$3:$AA$30,MATCH(C3, $W$3:$W$30, 0))," ")
O3:O4	O3	=IFERROR(INDEX($AB$3:$AB$30,MATCH(C3, $W$3:$W$30, 0))," ")
S2:S4	S2	=IFERROR((A2*M2) -(A2*M2*Q2),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C15:D15,C11:D13	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:E4	List	=$C$11:$C$15

 

[A Thayuman]

Apparently you seem to be working with Merged Cells ...

Merged Cells are a total disaster ...!!! and preventing you from working with Excel ...

Just get rid of them ...

Hi James the solution provided by Fulff as seen above works perfectly with merged cells - its great to have merged cells for formatting Thanks for your offering to help it is much appreciated

 

[smozgur]

Hi Fluff thanks works perfectly thanks for your help

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Fluff]

Hi Fluff thanks works perfectly thanks for your help

Glad we could help & thanks for the feedback.