INDEX/MATCH Not Working?

[Peter-27]

Hello all,

Why does my formula in cell J3 work, but cell K3 is not working? I would expect the returned value in cell K3 to be "Toyota Innovia (Manual)".

It's possible that I am simply not using these formulae correctly. What I am trying to achieve is the table on the right displaying the cheapest option (company/vehicle/cost) for different numbers of guests and durations. If you could suggest a more elegant solution, I would be all ears.

Many thanks,
Peter.

[Ace_XL]

The problem is merged cells in Col. A. The formula in K3 returns the value in Cell A14 which is technically blank although it doesn't appear so because of merged cells

If I were you, I'd unmerge all cells and repeat the vehicle name in Col A against each row. While I'm at it, I'd do the same for Col. B too

Also not sure why your ranges are inconsistent Rows(2:37) in Col J and Rows(10:37) in Col K

[Peter-27]

The problem is merged cells in Col. A. The formula in K3 returns the value in Cell A14 which is technically blank although it doesn't appear so because of merged cells

If I were you, I'd unmerge all cells and repeat the vehicle name in Col A against each row. While I'm at it, I'd do the same for Col. B too

Also not sure why your ranges are inconsistent Rows(2:37) in Col J and Rows(10:37) in Col K

So you are right that unmerging cells fixes the issue. However, I still remain confused why J3 returns A2 then, as A2 is also merged.

Surely both formulae (J3 & K3) should either work, or not work. One working and the other not is baffling to me.

[Sam Capricci]

J3 is finding the value that is directly across from 1200 which is in A2, K3 is looking for the value that is directly across from 1500 which is in D15, in A15 there is no value (if you unmerge the cell), hence the 0.

[Peter-27]

J3 is finding the value that is directly across from 1200 which is in A2, K3 is looking for the value that is directly across from 1500 which is in D15, in A15 there is no value (if you unmerge the cell), hence the 0.

Ahhh, that does make sense.

Thank you!

[Sam Capricci]

Glad to have helped! AND thank you for the rep!

[Peter-27]

Glad to have helped! If that takes care of your issue don't forget to mark the post as solved, AND thank you for the rep!

I'll mark it as solved, but just putting it out there that I am still open to responses about ways to make the solution more elegant and logical. I don't think the current solution I have implemented is efficient, even though it does work.

[Sam Capricci]

Then I wouldn’t mark it as solved because that likely will have most people pass the post.

What would be a solution you’ld like to see?