Index Match - Match returing worng row - sometimes

Bruce Johnson

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

Paul Newcome

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)

Bruce Johnson

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.