I am having problem in finding a closest match in the excel sheet attached here.

For example i write 39.45 in range F16, it gives closest match 39.5 but i need 39 to be value of range F18. moreover if the value of input equals the value in table it doesnot interpolate but give actual values.Array table is given from B41.

Hi If you are looking for the nearest value below the input value (F16) this will give you 39 as the result

=INDEX(B44:B354,MATCH(F16,B44:B354,1))

The last "1" tells the match to find the closest match below the lookup value.

If your input value is an exact match why would you need to interpolate?

re: "...in the excel sheet attached here"Please keep in mind that most individuals will be reluctant to download a file from an unknown source. I know that I won't do it on my own machines, and my corporate machine won't even let me access the website.

As much as we would like to be trusting and assume that you are coming here for help, we really have no way of being sure and therefore have to err on the side of caution.

Explaining your issue in this forum, and providing examples here, is probably the best way to get help.

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you Mr Altec for your support, if you have seen the sheet you would have noticed even if there is a perfect match even then it take inputs for interpolation. I want incorporate this error in index match formula when i used if command with it indexmatch formula didnt work.

Hi aj-khan I don't follow what you are saying. Can you give examples of input and expected results?

Have you tried the Index command I gave you?

AlteK, I believe that the OP wants you to download his spreadsheet via the link in his first post.

As I mentioned in my response, many of us are reluctant to download files from those that are essentially strangers. I suggested that the OP post the details in this forum for all to see.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad I have downloaded the file. I guess I have more faith (perhaps misplaced) in my virus detectors. I did make sure it was not a macro enabled worksheet before downloading.

This is the Index/Match equation the OP has in his sheet. I'm still not sure exactly what it is supposed to be doing.

{=INDEX(B44:B354,MATCH(MIN(ABS(B44:B354-F16)),ABS(B44:B354-F16),0))}Here are a couple of screenshots from the sheet.... (do you have the same restrictions in relation to viewing screenshots as you do with downloading?)

My corporate machine blocks most download and image hosting sites. I can try it at home.

Since your machine hasn't blown up (yet) maybe I'll download the file too.

Are you aware of the use of F9 within a formula?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad Always happy to be your guinea pig (canary?) for downloads.

No, I wasn't aware of F9. Just tried it and it replaced the formula with the result. Is that what's supposed to happen?

I did use "Evaluate Formula" but because a fairly large array is involved it was a bit confusing. I then broke down the formula into its components and I can sort of see what he is attempting but not absolutely certain.

I believe that the alternative I gave will resolve some of the OP's needs - essentially - for any input value in F18 the result desired is the closest match less than that input amount. The next sell down (F19) calculates the closest match higher than the input (in a different way and probably can be done with the the equivalent in F18 but with match_type=-1 [EDIT: just tested this and it didn't work]).

But this application has to do with the Viscosity of oil and I don't really understand the specifics of what the purpose is.

I look forward to your analysis.

While your use of F9 did what it was supposed to do, it's a bit more powerful than that. You can highlight a portion of a formula and then press F9 to evaluate just that portion of the formula right in the formula bar. That can sometimes help troubleshoot (or understand) a formula by seeing what the parts and pieces evaluate to.

The only rule is that the highlighted portion must be a "complete" formula, including all of the correct parenthesis, etc.

For example, highlighting ABS(B44:B354-F16) and pressing F9 will place the array in the middle of the formula. (I reduced it to B4:B14 so it wasn't so big)

You can press the X or use undo to get the formula back to the original.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Neat. It's an in-cell "Evaluate". How have I missed that feature in the past umpteen years? Could be partly due to my tendency not to use shortcuts ever since I had memorised hundreds of them for WordPerfect back in the day and then had to switch to Word. Clearly I was traumatised by that experience :-)

Okay - I think I've got it now - Thanks to DerbyDad's "trick" Here's what I believe the objective is;

The table array contains predetermined values for different viscosities. The objective is to calculate for values that do not appear in the table e.g.

The user wants to calculate viscosities for the value 39.45. The table contains values for 39 and 39.5. The objective is to return the 2 values - the ones directly lower and higher than the entered value so that the associated measures can be interpolated for the target.

The IndexMatch statement is taking the absolute difference of the each table entry minus the target value input in F16 and finding the minimum value of all those differences.So, in the example provided the target value is 39.45. The minimum point of the absolute differences appears at 39.5 where the OP really needs 39. But the difference between 39 and 39.45 is greater than the difference between 39.5 and 39.45.

That is why (if all the above is correct) my suggested formula will work - the index/match is looking for the closest match to 39.45 that is less than 39.45. In this case 39.45 will return 39.

As to the second problem;

if there is a perfect match even then it take inputs for interpolation. I want incorporate this error in index match formula when i used if command with it indexmatch formula didnt workI'm not sure what the user is after. I can see that if a perfect match is entered it will return the match as well as the next highest value. The interpolation still works in that it shows the correct values for the entered value.

I need to understand what the user wants to see when a perfect match is entered.

I'm not sure why we need any interpolation if an exact match is found. Seems like wrapping the "closest match" formula in a VLOOKUP would iallow the exact match to be used directly. P.S. I didn't have time to play with the spreadsheet last night and I can't now that I'm at work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You're right - it isn't needed but the OP says that when an exact match is entered it produces an error. I haven't been able to replicate that - for me, the "interpolation" cells return the correct values for the exact match.

You and I seem to be having a great conversation. Don't you think it would be nice if the OP joined us? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

yes. It's too bad - this is relatively interesting case and would have liked to reach a conclusion on it.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History