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

#### mikeVA

##### New Member
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.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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)))

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))

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.

You're very welcome.

Replies
1
Views
197
Replies
14
Views
705
Replies
1
Views
527
Replies
6
Views
337
Replies
3
Views
677

1,221,206
Messages
6,158,526
Members
451,497
Latest member
something68

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back