# INDEX MATCH stops returning values after X row

#### Rude Awakening

##### New Member
Hey All,
I have a conundrum,

I'm matching two values in a table and returning another value from that table to another
I have the below index match formula which works like a charm to a point. After the 37th value is returned, it wont return any more after that.

It would be easier to use the first formula as more data will be added to the main table and it saves changing formula numbering. Any ideas?

Formula1
Index/Match - Wont return values after the 37th

=IFERROR
(INDEX(Table251922242531346364[[#All],[CSI fields]] , <The column to Index>
MATCH(Table25192224253134636668697231676869120[@['#]]&\$B\$1, <matches a country in one table to a list of countries in another table>
Table251922242531346364[[#All],[Count]]&Table251922242531346364[[#All],[Country]]<matches a Column number in one table to a list of column numbers in other table>
,0)),"")

Formula2
Index/Match - Will return all required values

=IFERROR
(INDEX('All Country Fields'!E\$3:E\$943, <The column to Index>
MATCH(1,(\$B\$1='All Country Fields'!D\$3:D\$943)<matches a country in one table to a list of countries in another table>
*(Belgium!B3='All Country Fields'!C\$3:C\$943)<matches a country in one table to a list of countries in another table>
,0)),"")

EDIT:
On retest, formula 2 will only return as far as value 47

Last edited by a moderator:

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=XLOOKUP(Table25192224253134636668697231676869120[@['#]]&"|"&\$B\$1,Table251922242531346364[Count]&"|"&Table251922242531346364[Country],Table251922242531346364[CSI fields],"",0)``

#### Rude Awakening

##### New Member
Sorry @Fluff no joy.
I atttaced an excel here.
Basically in the CSI field columns of the country sheets , I need to look up the country(B1) and the #(col B) which relates to the count column in the All Country Fields sheet and return all the csi fields i.e. theres 47 for Belgium, 41 for Neatherlands and 55 for Switzerland

Mapping Doc

#### Fluff

##### MrExcel MVP, Moderator
As you don't need to refer to the name of the table the formula is in, just use
Excel Formula:
``=XLOOKUP([@['#]]&"|"&\$B\$1,Table251922242531346364[Count]&"|"&Table251922242531346364[Country],Table251922242531346364[CSI fields],"",0)``

#### Rude Awakening

##### New Member
Perfect, All fields populated as expected

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.
Depending on you are trying to do, if you get rid of the table you could use a formula like this
Excel Formula:
``=FILTER(Table251922242531346364[[CSI fields]:[Column9]],Table251922242531346364[Country]=\$B\$1)``
instead & it will spill down & across.

Replies
0
Views
69
Replies
2
Views
109
Replies
2
Views
781
Replies
7
Views
121
Replies
4
Views
116