Index and Match HELP!!

[mercmannick]

hi

can anyone see why this Formula wont work ?

=INDEX(LOOKUP!A5:A31847,MATCH(B7,LOOKUP!A5:A31847,0),MATCH(F7,LOOKUP!A5:D31847,0),MATCH(A4,LOOKUP!D4:D31846,0))

Cell E Worklist - week 21.xls
	A	B	C	D	E	F	G	H	I	J	K	L
4	L70	S03EL70									Material	S/S
5	Priority	Material	PONUM	Start	Finish	Phase	qty	Stat	GMPS	LOCATION	PromDate	Locate
6	1	D3215303000601	102609094	04.05.06	05.05.06	300	2	P	0	L70L70H11H81A30A04	#N/A	#N/A
7	2	D3215303000601	102609094	05.05.06	06.05.06	350	2	S	0	L70L70H11H81A30A04	#N/A	#N/A
8	3	D3215600500401	102617340	09.05.06	10.05.06	350	1	P	0	L70L70H11H81A30A04	#N/A
9	4	F3215502100001	101913182	10.05.06	11.05.06	250	2		GMPS	A30L70L70A30HT1L70L70H11H87A30H81	#N/A
10	5	D3215600500401	102617340	10.05.06	11.05.06	400	1	S	0	L70L70H11H81A30A04	#N/A
11	6	F3215502100001	101913182	11.05.06	12.05.06	300	2		GMPS	A30L70L70A30HT1L70L70H11H87A30H81	#N/A
L70

trying to get the value from matches of

IF A4 , B6, and F6 Match Sheet below then give me value from below Sheet Col C

Cell E Worklist - week 21.xls
	A	B	C	D
1
2			hours
3	partnumber	Phase	perbatch	M/c
4	D27550017005	100	5.10	M88
5	D27550017005	150	5.70	M88
6	D27550017005	200	2.27	M88
7	D27550017005	200	4.53	M88
8	D27550017005	250	4.75	M88
9	D27550017005	100	5.10	M85
10	D27550017005	150	5.70	M85
11	D27550017005	200	2.27	M85
12	D3215303000601	300	6.40	L70
13	D3215303000601	300	30.40	L70
14	D3215303000601	300	28.00	L70
LOOKUP

they must all match on the same line in lookup sheet

Many Thanks

Merc

 

[mercmannick]

Cell E Worklist - week 21test.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	M85	S03EM85									Material	S/S
5	Priority	Material	PONUM	Start	Finish	Phase	qty	Stat	GMPS	LOCATION	PromDate	Locate
6	1	L57252725000	102535398	01.04.06	02.04.06	200	1					1.30
7	2	D5725015800101	102504219	11.04.06	12.04.06	300	1					#N/A
8	3	D5725015800101	102504220	11.04.06	12.04.06	300	1					#N/A
9	4	D5725015800101	102504221	11.04.06	12.04.06	300	1					#N/A
10	5	D5725015900101	102599441	11.04.06	12.04.06	300	1	W				#N/A
11	6	D5725015800101	102504222	12.04.06	13.04.06	300	1					#N/A
12	7	D5725015800101	102504223	13.04.06	18.04.06	300	1					#N/A
13	8	D5725015900101	102504551	13.04.06	18.04.06	350	1	W				#N/A
14	9	D5725015800101	102504224	21.04.06	22.04.06	300	1					#N/A
M85 List

 

[Aladin Akyurek]

Would you please run the TrimAll macro on both sheets...

 

[mercmannick]

OK

i am looking for it now

thanks

Merc

 

[mercmannick]

Sorted Thanks Aladin

all i need to do now is stop th non matches showing #N/A

Merc

 

[Aladin Akyurek]

Sorted Thanks Aladin

Great.

all i need to do now is stop th non matches showing #N/A

I've already mentioned some alternatives. One with morefunc's SETV and GETV would allow you not to degrade performance for the formulas with them do not calculate the same thing twice.