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

 

[Aladin Akyurek]

=INDEX(LOOKUP!$C$4:$C$14,MATCH(1,IF(LOOKUP!$D$4:$D$14=$A$4,IF(LOOKUP!$A$4:$A$14=B6,IF(LOOKUP!$B$4:$B$14=F6,1))),0))

which must be confirmed with control+shift+enter (not just with enter) and copied down.

 

[mercmannick]

Aladin

Amazing thnx m8

Can you break down your Formula so i can understand and learn from it

Also can i put isna or iserror to not show non matches as #N/A's

Merc

 

[Aladin Akyurek]

Aladin

Amazing thnx m8

Can you break down your Formula so i can understand and learn from it

Also can i put isna or iserror to not show non matches as #N/A's

Merc

=INDEX(LOOKUP!$C$4:$C$14,
       MATCH(1,
             IF(LOOKUP!$D$4:$D$14=$A$4,
                IF(LOOKUP!$A$4:$A$14=B6,
                   IF(LOOKUP!$B$4:$B$14=F6,1))),0))

INDEX is given a return range: C4:C14 and needs a position value.

The MATCH bit calculates the needed position from a calculated match range, based on multiple conditions:

Evaluate if D-cells are equal to A4. If so,
evaluate if corresponding A-cells are equal to B6, If so:
evaluate if corresponding cells are equal to F6. If so, return 1 otherwise FALSE.

Thus the IF bits taken together returns something like:

{FALSE;1;FALSE;FALSE;1}

MATCH(1,{FALSE;1;FALSE;FALSE;1},0)

returns 2 for the first 1 is at position 2 in {FALSE;1;FALSE;FALSE;1}.

INDEX, with 2 handed to it, returns the value at position 2 in the return range, yielding the desired result.

A set of FALSE evaluations would make MATCH fail, hence #N/A.

If you have the morefunc.xll add-in installed, you'd have the most efficent way of avoiding #N/A...

=IF(ISNUMBER(SETV(MATCH(1,
             IF(LOOKUP!$D$4:$D$14=$A$4,
                IF(LOOKUP!$A$4:$A$14=B6,
                   IF(LOOKUP!$B$4:$B$14=F6,1))),0))),
      INDEX(LOOKUP!$C$4:$C$14,GETV()),
      "")

confirmed of course with control+shift+enter.

Otherwise use a 2-cell approach:

Y2:

=MATCH(1,
       IF(LOOKUP!$D$4:$D$14=$A$4,
          IF(LOOKUP!$A$4:$A$14=B6,
             IF(LOOKUP!$B$4:$B$14=F6,1))),0)

confirmed with control+shift+enter.

Y2:

=IF(ISNUMBER(X2),INDEX(LOOKUP!$C$4:$C$14,X2),"")

which just needs enter.


The next version of Excel seems to introduce IFERROR, which allows one to construct:

=IFERROR(INDEX(LOOKUP!$C$4:$C$14,
       MATCH(1,
             IF(LOOKUP!$D$4:$D$14=$A$4,
                IF(LOOKUP!$A$4:$A$14=B6,
                   IF(LOOKUP!$B$4:$B$14=F6,1))),0)),"")

 

[mercmannick]

superb thnx Aladin

cant work out how it works fine on 1 sheet , try it on another sheet exactly the same layout and it fails

Any idea where i am going wrong ?

Merc

 

[Aladin Akyurek]

superb thnx Aladin

cant work out how it works fine on 1 sheet , try it on another sheet exactly the same layout and it fails

Any idea where i am going wrong ?

Merc

What formula did you implement on the other sheet where it fails? BTW, what result did you get that you qualify as failure?

 

[mercmannick]

Aladin i used your formula on other sheets , and it returns #N/A

but i have done a manual check and it should return the values as lookup sheet contains them

(yet on first sheet it works perfectly)


Merc

 

[Aladin Akyurek]

Aladin i used your formula on other sheets , and it returns #N/A

but i have done a manual check and it should return the values as lookup sheet contains them

(yet on first sheet it works perfectly)


Merc

Hope you didn't forget control+shift+enter...

 

[mercmannick]

No Aladin same on first heet as in other 4 sheets, what i cant work out is how can it work on sheet 1 and not on other 4 sheets ?

Merc

 

[Aladin Akyurek]

No Aladin same on first heet as in other 4 sheets, what i cant work out is how can it work on sheet 1 and not on other 4 sheets ?

Merc

You still didn't say what results you get...

A possible reason can be that you have unwanted chars around the relevant entries like spaces. You could try to clean up the data with the TrimAll macro whose is available in some of the threads on this board.