MATCH/INDEX Problem

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
OK, I have a named range in cells C20:V20 and the name of the Range is "PV". The values are as follows:<pre>
0;$7,109;$13,927;$20,466;$26,738;$32,754;$38,525;$44,060;$49,369;$54,462;$59,348;$64,034;$68,530;$72,843;$76,981;$80,950;$84,759;$88,412;$91,918;;$95,281;$98,508</pre>

Now, my ultimate goal is interpolate between these values which are for years 0 - 20. I intend to use
the result of a MATCH to tell me my years. e.g. MATCH - 1 will give me 0 years if the value I'm looking for is $2,000.

I am using a formula like this to return my values:

=INDEX(PV,1,MATCH(PV,A4,1))

Where:

PV is my named range
A4 houses the value to be interpolated.

I have got my interpolation formula working, so that isn't an issue. What is the issue is that all of my
results using the above formula are 0. i.e. I put this formula in B4 and copied down the column for values $2,000 through to $50,000.

What am I doing wrong with this INDEX/MATCH formula?
It just seems to be giving me the smallest value in the named range when it can't find an exact match.

TIA



_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-09-13 10:40
This message was edited by Mark O'Brien on 2002-09-13 10:41
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
Never mind, I'm just a retard that kept reading the Help File wrong. I put my array and lookup value arguments round the wrong way.

:p

Thanks
 

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
Ok.
Here's how it's done.

Right the following words on a piece of paper:

We
Todd
Did

Now read the three words quickly. Do this 3 or 4 times until you understand.
That's what you are.
And we love you for it.
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
ToddWeDid
 

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,603
Okay, real version for somebody who looked and PM'd me about it:

Write this:

I
M
sofa
king
we
todd
did

Now, read it, out loud....quickly! LOL
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,094
Members
412,441
Latest member
kelethymos
Top