Best match function to retreive the closest value based on 2 columns

[Aspe]

Hi all,

I'll start by saying that I'm very new at this and don't even know if this is possible.
I've been trying to figure out how I can writte a function that retrieves a best match result from a table looking like this:

A	B	C
11 745 860	2 107	0,018525325
18 047 995	3 363	0,015729268
19 006 145	3 499	0,014773922

I would like to input 2 values, one for column A(14 000 000) and one for B(3 000). The function should then determine which row that is closest to my values and return column C.
So far INDEX and MATCH functions seem to give column A the benefit and don't really take column B into consideration.

Hope someone can help me out.

Regards,
/Aspe

[Pete_UK]

It would help if you posted the formula that you have used (or a sample workbook). Which of the two criteria would take priority? For example, if you were looking for 12 000 000 in column A it is clearly closer to A1, but if B were 3 000 that is clearly closer to B2 - how would you resolve this?

Also, it is not clear if those values in columns A and B are numbers (where you have just inserted spaces for clarity in this post) or text values (which is how Excel would treat them if you had spaces in there).

Pete

[Aspe]

Hi Pete,

Thanks for answering.
The formula that I have tried is: =INDEX(C2:C4;MATCH(1;(A2:A4>E1)*(B2:B4>F1);0)) where E1 and F1 are my input columns.

To answer your question about priority:
I don't want any of them to take priority, basically I would like the formula to find the row where both input values are as close as they can be.
If we pretend that I have 10000 rows and:
1. The value for column A is very close to row 3 but the value for column B is far away
2. The value for column B is very close to row 8988 but the value for column A is far away
3. Both values are "close" to row 5699, then I want column C for that row.

What I'm trying to say is that I prefer if both values are at a 90% accuracy, rather than one at 99% and the other at 20%.

The values are all numbers, I just inserted spaces so that it would be easier to read. Sorry about that.

Regards,
/Aspe

[Pete_UK]

Well, I think you would have to measure how far away you are from the found_value in column A and the next value, and for the found_value in column B and its next value (assuming both columns are sorted in sequence), and then a judgement would still have to be made in the formula as to whether these differences are "acceptable", and which one to take. I'm not sure how you would achieve that with a formula, but it strikes me as potentially being very messy.

Hope this helps.

Pete