Look up a value in a table using both exact and approx matching

[Guest]

Hope the table below is still correctly formatted. You should see the top
row with 4 distinct "exact" year values (25, 30, 35, 40) and the left most
column with "approximate" percentage values. I need a formula that will
lookup this table to return the following result:
30 years at 83.5% will use 1.95% as the value in a formula.
25 30 35 40
100% 3.10% 3.30% 3.50% 3.70%
95% 2.75% 2.95% 3.15% 3.35%
90% 2.00% 2.20% 2.40% 2.60%
85% 1.75% 1.95% 2.15% 2.35%
80% 1.00% 1.20% 1.40% 1.60%
75% 0.00% 0.00% 0.00% 0.00%

Hope to hear from someone soon
Tks Loads

 

[Guest]

Let's say your data from A1:E7
In G1: holds 30
In H1: holds 83.5%

=INDEX(A1:E7,MATCH(H1,A1:A7,-1),MATCH(G1,A1:E1,0))

 

[Guest]

Hi Teethless Mama
The formula you provided keeps returning an error message yet it appears to
be exactly what I need. I copied my information into the exact range you
used in your formula and copied your formula into cell G3. Once I get the
error message and click OK - the part of the formula that is highlighted is
E7.MATCH.

I tried using the function wizard but just can't seem to get it right.
By the way, the table was unformatted so just to clarify, cell A1 is empty,
the number "25" is incell B1
I hope you can help me. Tks so much

 

[Guest]

Yes, 25 in B1, and A1 is empty. Make sure all cells are not including leading
and trailing spaces. The formula I provided to you should work. I tested last
night.

 

[David Biddulph]

It's always better to copy and paste a formula, rather than typing it in and
mistyping it.

That said, the error message is apparently telling you exactly where you
went wrong. You said that it highlighted ... E7.MATCH ..., whereas the
formula suggested (which you haven't shown below because you replied to your
own message rather to the one including the suggestion) said ... E7,MATCH
.....

 

[Guest]

hi again,
Sorry - no go - it just won't work. I even tried retyping the formula (vs
copying) and I keep getting the same error - somehow it doesn't seem to like
the "match" function within the index function. I' m new to community posts
- but is there a way to attach the worksheet to a post so that you could
actually see what I'm looking at. I'd love to even attach a screen shot of
the message I'm getting and what the formula looks like after I get the
message.
What say you.
Tks again

 

[Guest]

David and Teethless mama - IT WORKED.
I figured out the problem. It wanted a semi-colon everywhere that the
formula had a comma. I changed them all and the result was correct. I
tested a few times and returned correct results consistently.

Thank you all for your help and support - you've made my day. I really
needed this to work.

 

[vezerid]

Any chance your settings require semicolon ( for separating
arguments? The highlighting is pointing to this direction...

HTH
Kostis Vezerides

 

[Guest]

Yes - that's exactly it and if I hadn't already found that solution, you
would have solved this for me for which I thank you.
I think I must not be using the posting process correctly. I posted my
finding about the semi-colon - can you not see it.
Tks again