Reference function from an out of sort table
 

I'm looking for a reference function to pull the result from an out of sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number 0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin

Reference function from an out of sort table
 

On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier
wrote:

I'm looking for a reference function to pull the result from an out of sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number 0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin


Try this modifying your second formula like this

=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10, 1),2)

or, a little simpler, just

=INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 ))

Hope this helps / Lars-Åke

Reference function from an out of sort table
 

If A14 = 3125, then result in B14 is 0.675.

What is the "rule" for finding the correct result? Is it: the closest value
that is less than the lookup value?

--
Biff
Microsoft Excel MVP


"AFSSkier" wrote in message
...
I'm looking for a reference function to pull the result from an out of
sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number
0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin

Reference function from an out of sort table
 

Lars-Ã…ke,

Your suggestion for using the Floor function works perfectly. The following
formulas work great for what I needed.

=INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 ))
or =VLOOKUP(FLOOR($A$14,1000),$A$2:$B$10,2,FALSE)

--
Thanks, Kevin


"Lars-Ã…ke Aspelin" wrote:

On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier
wrote:

I'm looking for a reference function to pull the result from an out of sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number 0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin


Try this modifying your second formula like this

=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10, 1),2)

or, a little simpler, just

=INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0 ))

Hope this helps / Lars-Ã…ke
.

Reference function from an out of sort table
 

Although this can be done (of course), you (or your principal) are making it
difficult. Why not sort the table (ascending) so you can use standard
VLOOKUP functionality?

If that is not possible, please give some more information about the (type
of) problem you are trying to solve; we are just not prepared to believe
things should be that difficult.:-)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"AFSSkier" wrote in message
...
I'm looking for a reference function to pull the result from an out of
sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number
0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin

Reference function from an out of sort table
 

I would be a lot easier if the table was not shared or if it was a perfect
world. But as you know, we're all in end-user hell & it's not a perfect
world.

The table is imported in ascending order. But the end-users are able to
sort as they need it printed.

I know my posted example doesn't reflect this, I simplified it for
illustration.

--
Thanks, Kevin


"Niek Otten" wrote:

Although this can be done (of course), you (or your principal) are making it
difficult. Why not sort the table (ascending) so you can use standard
VLOOKUP functionality?

If that is not possible, please give some more information about the (type
of) problem you are trying to solve; we are just not prepared to believe
things should be that difficult.:-)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"AFSSkier" wrote in message
...
I'm looking for a reference function to pull the result from an out of
sort
table (see table example below). The result should always come from the
reference value LT A14. The table is always a whole number
0,1000,2000,3000,
etc.

I've tried the following functions, but they all require data in ascending
order and/or exact match of the value in A14.
=VLOOKUP($A$14,A$2:B$10,TRUE)
=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2)
=LOOKUP($A$14,A$2:A$10,B$2:B$10)

Table example:
A B
2 1000 0.525
3 6000 0.946
4 3000 0.675
5 0 0.457
6 4000 0.746
7 2000 0.606
8 8000 1.29
9 5000 0.835
10 7000 1.09

If A14 = 3125, then result in B14 is 0.675.

I don't want to have to use a complex IF/AND, like:
=IF(AND($A$143000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"contin ue
IF/AND")

Thanks, Kevin