problem with the match_type in =match

If the values always increase by 5 you could simply add 2,5 to your search value.

Hello Dan,

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; font-size:9pt; text-align:right; ">35</td><td style="text-align:right; ">40</td><td style="text-align:right; ">45</td><td style="text-align:right; ">50</td><td > </td><td style="background-color:#ffff00; text-align:right; ">4</td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=MATCH(ROUND<span style=' color:008000; '>(49/5,0)</span>*5,A1:D1,1)</td></tr></table></td></tr></table>

Hi Dan

If the numbers are not evenly spaced (or not even sorted) you can use

for the closest match, in b4:

=INDEX($B$1:$F$1,MATCH(MIN(ABS($B$1:$F$1-B3)),ABS($B$1:$F$1-B3),0))
This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

if you prefer the column of the closest match, in b6:

=MATCH(MIN(ABS($B$1:$F$1-B3)),ABS($B$1:$F$1-B3),0)
... also confirmed with CTRL+SHIFT+ENTER.

Hope this helps
PGC

Thanks for the quick response, it was very helpful and works on the example I gave. Unfortunately, I'm new to this board and had problems figuring out how to post a picture of my workbook to give a more practical example of what I'm working with.

Hopefully this example directly out of my workbook will help:

I hope this worked.

For those not from Germany, Vergleich=match. Basically, the field in red are good examples of my problem. I want to find the value of TL90 in the Matrix "Growth Class". Rounding would help in some situations but not all of them. The formula Beate gave me was great for my first example but I don't know how to apply it to my situation.
I hope the html maker works
Thanks for any help,
Dan

sorry pgc01, I didn't see your reply before I sent my last message. I'll try your solution too and see what happens.

Hi again

I'd like to say that you could use my 2nd formula but I am obviously missing something.

You said in your first post "I would like to have the function find the closest match regardless if the number is higher or lower that what I'm searching for."

- In row 3, the value is 86.4 and you choose class 6 (78) instead of class 7 (92). 86.4 - 78 = 8.4, 92 - 86.4 = 5.6. Why class 6? Isn't class 7 the closest match?
- In row 4, the value is 60.3 and you choose class 7 (58) instead of class 8 (62). 60.3 - 58 = 2.3, 62 - 60.3 = 1.7. Why class 7? Isn't class 8 the closest match?

Can you please explain?
My 2nd formula would give for those 2 rows 7 and 8, the closest matches.

Kind regards
PGC

Thanks for the reply,

You pointed out my exact problem. I would prefer that the formula for row 3 gives the result of column 7 instead of column 6. The problem lies in the fact that the match formula, when NO exact match is found, will always give the column with next smallest match, hence column 6 in row 3, and column 7 in row 4.

I would like to have a formula give me the column with the closest match, but the results in the column "growth class" are the results of the match formula. (=match(D3,H3:R3,1)

I will continue with you formula, but unfortunately I'm getting the value error. Unfortunately I'm not too experienced with complicated formulas, I've mastered the If function and thats about as far as I go.

Thanks for the insightful replies!
Dan

You are getting the value error because you are not confirming the formula with CTRL+SHIFT+ENTER.

For your example, in E3:

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

If you confirm just with ENTER you'll get the #VALUE! error.

You can then copy it down.

Plese try it.

Hope this helps
PGC

Hello Dan,

Formula from cell E3 can be copied down.

And in german Excel the formula in E3 is:
{=VERGLEICH(INDEX(H3:R3;VERGLEICH(MIN(ABS(H3:R3-D3));ABS(H3:R3-D3);0));H3:R3;1)}
(Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!)


Pls refer to: http://www.excelformeln.de/formeln.html?welcher=65