A
AFSSkier
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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue
IF/AND")
Thanks, Kevin
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$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue
IF/AND")
Thanks, Kevin