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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

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,607
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,607
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
 

Forum statistics

Threads
1,147,514
Messages
5,741,608
Members
423,672
Latest member
ajj13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top