Excel Formula assistance needed

[AggieMike88]

I need help figuring out how to use Excel to pick out a value from an array/matrix.

What I want to do is have the forumla return the value in column 4 if the "index" value is between the values of columns 1 and 2.

13,000 -- 25,000 ..... 7.50%
10,000 -- 13,000 ..... 6.75%
08,000 -- 10,000 ..... 6.00%
00,000 -- 08,000 ..... 5.00%
​

For example, If the index value is 12,300, I want to have 6.75% returned. If the index value is 500, 5.00% would be returned.

Any help?

 

[JeffDG]

I need help figuring out how to use Excel to pick out a value from an array/matrix.

What I want to do is have the forumla return the value in column 4 if the "index" value is between the values of columns 1 and 2.

13,000 -- 25,000 ..... 7.50%
10,000 -- 13,000 ..... 6.75%
08,000 -- 10,000 ..... 6.00%
00,000 -- 08,000 ..... 5.00%
​

For example, If the index value is 12,300, I want to have 6.75% returned. If the index value is 500, 5.00% would be returned.

Any help?

The magic formula you are looking for is VLookup

You would set up your data as:
A B
0 .05
8000 .06
10000 .0675
13000 .075


Then you would have in Column A somewhere else

A 12,375
B =vlookup(a1,Range,2,true)

What this will do is look for the value in A1 within Range and return the second column from the Range. The True lets it search for non-exact matches, so if A1 >0, but < 8000, it will return 5%, >8,000 and < 10,000 6%, etc.

 

[JeffDG]

Here's a sample:

 

[astanley]

Yup, that is the exact approach. Quick on the link, Mr. Jeff.