Index match not working for last 2 rows.

Boniouk

Board Regular
Joined
Aug 2, 2013
Messages
166
Here is my formula.

=INDEX(Tracker2!C$72:C$151,MATCH('Field Results Data'!$A14,Tracker2!$A$72:$A$151)+2,1)

The index is all the data between C72 and C151. I only need column C so this is ok, and the data is actually within 72 to 130 so there is no issue with missing off what i need.

The match is A14 which is to the left and is an ID, eg ABC123.
and tracker 2 A72-A151 is where all the IDs are kept.
the first 2 columns in the tracker data are empty which is why I have +2 to get the 3rd row after the ID.

I have this formula repeated on several pages and it works for all but the last 1 or 2 every time. Surely if its working for the first 8 or 9 it cannot be a formula issue. So confused!!!

Any ideas?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Maybe it should be

=INDEX(Tracker2!C$72:C$151,MATCH('Field Results Data'!$A14,Tracker2!$A$72:$A$151,0)+2,1)
 
Upvote 0
the +2 is breaking it, i assume you get a #Ref error?

INDEX(Tracker2!C$72:C$151,MATCH('Field Results Data'!$A14,Tracker2!$A$72:$A$151)+2,1)


e.g. if you find the value of A14 in A151 from the range
Tracker2!$A$72:$A$151, which would give you 79, but you add 2 to get 81

You then say to get the 81st value from
Tracker2!C$72:C$151 which does not exist
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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?

Disable AdBlock

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
Back
Top