INDEX & MATCH returning N/A

[Sazzle14]

HI (Office 2007)

I feel I have gone round in circles on this one. Here is the formula and the result is #n/a.

The cell C3 is in the middle of the array, and my understanding is that the Match formula would work out what row it is on, which will consequently feed back in to my Index formula.

What am I doing wrong?

=INDEX('R1 MasterData'!$A3:$IK2001,((MATCH(Transactions!$C4,'R1 MasterData'!$G4:$IK82,FALSE))),7)

I am trying to put this into cell b4 of the Transactions! sheet

thanks

Sarah

 

[barry houdini]

Hello Sarah,

with MATCH function the range must be a single row or column so you can't use the range 'R1 MasterData'!$G4:$IK82. Do you know where Transactions!$C4 will occur in that range, can it be anywhere or will it be within column G or row 4 or something else?

 

[Sazzle14]

ahh I see - well there in lies the problem C4 could be in any row or column within 'R1 Master Data sheet

any ideas?

 

[barry houdini]

Can it occur more than once in that range.....or once at most?

 

[Sazzle14]

only once

 

[barry houdini]

OK, try this "array formula"

=INDEX('R1 MasterData'!$G4:$IK82,SMALL(IF(Transactions!$C4='R1 MasterData'!$G4:$IK82,ROW('R1 MasterData'!$G4:$IK82)-ROW('R1 MasterData'!$G4)+1),1),SMALL(IF(Transactions!$C4='R1 MasterData'!$G4:$IK82,COLUMN('R1 MasterData'!$G4:$IK82)-COLUMN('R1 MasterData'!$G4)+1),1))

confirmed with CTRL+SHIFT+ENTER

 

[Sazzle14]

the answer returned is 0

 

[Dryver14]

Fairly long winded but this finds what row C4 is in my little array

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O
1													1
2													2
3													3
4	9		2	3	4	5	6	7	8				4
5			7	7	7	7	7	2	1				5
6			7	7	7	7	7	3	2				6
7			7	7	7	7	7	4	3				7
8			7	7	7	7	7	5	4				8
9			7	7	7	7	7	9	5			9	9
10			7	7	7	7	7	6	6				10
11
12												9
Sheet1

 

[Aladin Akyurek]

ahh I see - well there in lies the problem C4 could be in any row or column within 'R1 Master Data sheet

any ideas?

What is the range from which the result must come?