Closest Match Cross Reference Table

[Jonathan78]

Afbeelding 1.gif

Hello all,

I'm trying to get the closest match from a cross reference table as the one above, but can't get it done.
Any ideas?

[:) Sixthsense :)]

In H5 cell

Please Login or Register  to view this content.

[Jonathan78]

Thanks for trying but that didn't work

[Kevin UK]

Hi Jonathan78

Try the INDEX & MATCH:

Please Login or Register  to view this content.

[Jonathan78]

Hello Kevin,
Thanks for helping.
I tried that one already but it always returns the highest value.
Changing the 1 to -1 always gives the lowest.
I am looking for the closest

[oeldere]

@Jonathan78,

Post your file instead of an .gif file.

Then we can test the formula.

[Kevin UK]

Hi Jonathan78

That gives me 36!

Closest Match.PNG

[Jonathan78]

Book1.xlsm

Hello Kevin it does, but when I put 7550 in H2 it remains 36 iso changing to the nearest (42)

[oeldere]

I used the orange cells as helpcells.

The result is in the green cell.

See the attached file.

If the value is as close to both cells it takes the lowest value.

[:) Sixthsense :)]

Thanks for trying but that didn't work

Don't say something just like that, can you prove it?

[Jonathan78]

Don't say something just like that, can you prove it?

It's the same as with, index match, it does not give the closest but the lowest value.

[Jonathan78]

I used the orange cells as helpcells.

The result is in the green cell.

See the attached file.

If the value is as close to both cells it takes the lowest value.

Hello oeldere,
Thanks this one works.
strange though that there is no simple solution for it in excel, without the use of helpcells.

[oeldere]

Please Login or Register  to view this content.

It can be done in 1 formula, but I think it's much easier to understand if you use (my) helpcells.

[Jonathan78]

It can be done in 1 formula, but I think it's much easier to understand if you use (my) helpcells.

Many thanks for your help!

[Ghozi Alkatiri]

try it
=INDEX($C$3:$F$10;MATCH(H2;$B$3:$B$10);MATCH(H3;$C$2:$F$2))