INDEX MATCH stops returning values after X row

Rude Awakening

New Member
Joined
Jul 10, 2014
Messages
29
Office Version
  1. 365
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:

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=XLOOKUP(Table25192224253134636668697231676869120[@['#]]&"|"&$B$1,Table251922242531346364[Count]&"|"&Table251922242531346364[Country],Table251922242531346364[CSI fields],"",0)
 

Rude Awakening

New Member
Joined
Jul 10, 2014
Messages
29
Office Version
  1. 365
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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,253
Messages
5,576,978
Members
412,755
Latest member
Alishk
Top