Index Match  Match returing worng row  sometimes
Hi Smartsheet Gurus
I have a sheet that has a listing of few hundred projects and a column for each month with the number of hours being charged for each project for that month  some have values, some have a value of zero and there are no blanks.
I am doing an =Index(Match  formula looks like  =INDEX([Project Name]$10:[Project Name]$388, MATCH(JAN@row, January$10:January$388), 1)
I have a column "JAN" that uses large to find the first largest number in the January column month and next row =Large(January10:January388,1) ,next row =Large(January10:January388,2) so and so forth for 20 rows
The Large works as expected  I have a listing of hours for January of the top 20 hours greatest to smallest.
The Index(Match  not so good  it returns the first couple correctly then skews and repeats see the output  it is actually the Match that is skewing the result  I inserted a column and used the Match portion to see what the row index would be and you can see where the problem starts occuring.
I put the formulas in to the heading of each column without the =
The layout of the source is as follows
Note: the column index is a formula as the actual row is 10  so the range starts at 10 and goes to 388
What I noticed is the last found Gum Springs  Migrate... is correct and the next value is the row above this one  Google Scripts and the hours value is 0  so it is not finding the value of 156 (ther are not multiple 156 values in the column)
here is the rows where it skews the result
it is supposed to match 156 on row index 198  also note that there are a repeat of the same google script error repeated 6 times  there are several more errors but is a repeat of the same scenario
Also, note I created 2 additional columns as an experiment next to the Januey and Jan column and used the =Value to ensure the values were numbers.
So I am stumped and I appreciate any assistance/guidance that you can give me.
regards, bruce
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Answers

Try leveraging the 3rd portion of the MATCH function to specify an exact match:
=INDEX([Project Name]$10:[Project Name]$388, MATCH(JAN@row, January$10:January$388, 0), 1)

Hi Paul, yeah I forgot to show that  I did trry that to no avail. I ended up using VLOOKUP to solve this works well  BUT  I am still curious as to why this is occuring  maybe some defect of sorts but all it is doing is returning text elements  I will continue to investigate and if I find what the cause is I will let the community know. thanks much.
Bruce Johnson
Director Portfolio, Project Methods & Governance
Veolia North America
Boston, MA
Help Article Resources
Categories
Check out the Formula Handbook template!