Common index match problem, conversion to if/rows please.

[mikeVA]

I have a common problem. I want to index/match across a range larger than one column.

Here is what I have:

{=INDEX('Clinical Trending (Var)'!$F$307:$F$322,MATCH(X29,'Clinical Trending (Var)'!$G$307:$G$322,0),0)}

Here is the range that doesn't work:
{=INDEX('Clinical Trending (Var)'!$F$307:$F$322,MATCH(X29,'Clinical Trending (Var)'!$G$307:$R$322,0),0)}

The G to R block. I tried to convert this to if/countif/row format, but I'm a noob. Can someone please help with a proper array formula. I have the index in column F. I have a value to match in cell X29 and I want to match it across the range G307 to R322. It will match once exactly in one of those cells on one of those rows in 307 to 322. I will copy the formula down so that x29 will become a new value every time to match.

Thanks for any help.

I tried something like this:
{=IF(COUNTIF(A2:C6,A8),INDEX(D:D,MAX(IF(A2:C6=A8,ROW(A2:C6)))),"")}
But got a REF# error.

 

[bschwartz]

Hi MikeVA,

Try this formula:

=INDEX('Clinical Trending (Var)'!$F$307:$F$322,SUMPRODUCT(('Clinical Trending (Var)'!$G$307:$R$322=X29)*(ROW('Clinical Trending (Var)'!$F$307:$F$322)-ROW('Clinical Trending (Var)'!$F$307)+1)))

 

[FDibbins]

For a start, I doubt you need an ARRAY formula for this (using {})

This sounds like a regular INDEX/MATCH/MATCH formula. Assuming your columns have headings....

	A​	B​	C​	D​	E​
1​		one	two	three	four
2​	aa	1​	10​	100​	1000​
3​	bb	2​	20​	200​	2000​
4​	cc	3​	30​	300​	3000​
5​	dd	4​	40​	400​	4000​
6​
7​	bb	200​
8​	three

B7=INDEX($B$2:$E$5,MATCH(A7,$A$2:$A$5,0),MATCH(A8,$B$1:$E$1,0))

 

[mikeVA]

Thank you both for the smart answers. They are perfect. I need to return the row header, but the regular index match might work with some helpers.

 

[bschwartz]

You're very welcome.