INDEX MATCH formula works perfectly in 1 workbook, but returns #REF! error in another

[Darkshine31]

Good afternoon folks.

I have uploaded 2 snaps of the related formulas here. I works and 1 does not. I'm at a loss as to why. Any help would be much appreciated.

Thanks

 

[Joe4]

I think you have your ROW and COLUMN references mixed up in the INDEX function.
Try this for that part:

INDEX(E504:H504,1,MATCH(MIN(E520:H520),E520:H520,0))

You want to pull from the first row since you only have one row in your INDEX range (hence the 1 in the second argument, and whatever column the match is found in.

 

[Darkshine31]

I think you have your ROW and COLUMN references mixed up in the INDEX function.
Try this for that part:

INDEX(E504:H504,1,MATCH(MIN(E520:H520),E520:H520,0))

You want to pull from the first row since you only have one row in your INDEX range (hence the 1 in the second argument, and whatever column the match is found in.

Thank you Joe4.

I still don't understand why it works on one workbook and not the other but your solution solved my problem, so thank you very much for your time.

 

[Joe4]

I would have to see your other data and example. Maybe there you were dealing with a single-column, multi-row range, where here you have a single-row, multi-column range.
In your original formula, since you had the calculation in the row argument, you were literally telling it to return the second row of a one row range, which is not possible, and hence the REF error!

 

[Darkshine31]

I would have to see your other data and example. Maybe there you were dealing with a single-column, multi-row range, where here you have a single-row, multi-column range.
In your original formula, since you had the calculation in the row argument, you were literally telling it to return the second row of a one row range, which is not possible, and hence the REF error!

Snap1 - Working (attachment) is the original working formula

 

[Joe4]

As you probably discovered, you cannot attach files to this site. You can post screen images though, like you did in your first post.

 

[RoryA]

The first one working was pure luck. The match was in the first column, so INDEX(range, column, 1) was equivalent to INDEX(range, 1, 1) which works with a 1 row range. As soon as the match is in a different column, you're passing a number greater than 1 as the row argument.

 

[Joe4]

The first one working was pure luck. The match was in the first column, so INDEX(range, column, 1) was equivalent to INDEX(range, 1, 1) which works with a 1 row range. As soon as the match is in a different column, you're passing a number greater than 1 as the row argument.

I think I may have missed that your first image in your first post is the one that works. I didn't realize that was the one you were talking about, as it did not look like the same formula.
When I originally opened the PDF, the menu options covered up the portion of your formula with the INDEX/MATCH formula.

Thanks for clearing it up, Rory!

 

[Darkshine31]

The first one working was pure luck. The match was in the first column, so INDEX(range, column, 1) was equivalent to INDEX(range, 1, 1) which works with a 1 row range. As soon as the match is in a different column, you're passing a number greater than 1 as the row argument.

Right! Now I completely understand. Thank you RoryA.

 

[Darkshine31]

I think I may have missed that your first image in your first post is the one that works. I didn't realize that was the one you were talking about, as it did not look like the same formula.
When I originally opened the PDF, the menu options covered up the portion of your formula with the INDEX/MATCH formula.

Thanks for clearing it up, Rory!

That's perfectly fine Joe4, I have appreciated your continued assistance.