I'd like return a value from a table, using Vlookup, where a number is more
than the amount in column A and less than the amount in column B, and the
column lookup offset is determined by referencing data in that's been
entered in a different cell (not in the table, but in the same row as this
formula I'd like to create). Is this possible, or is there a workaround
for the greater than and less than columns? TIA,

Buckwheat.

Are you using continuous ranges in ascending order? or ranges that
are continuous within the granularity of your data? for instance:

A B
1 0 1.00
2 1.01 2.00
3 2.01 3.00

If so, you only need to do a VLOOKUP() starting in column A,
ignoring B, but using TRUE as the fourth parameter (assume J1 has
the lookup value and J2 the column offset):

=VLOOKUP(J1, A:G, J2, TRUE)

Adjust the column offset as needed.

If your data is not continous, but also is not overlapping, e.g.:

A B

1 0 1
2 1.5 2.5
3 4 7

then you can use a SUMPRODUCT():

=SUMPRODUCT(--(A1:A3<J1),--(B1:B3>=J1),OFFSET(C1,0,J2,3,1))



In article <Xns9454910FEDF0BBuckwheat@68.12.19.6>,
Buckwheat <(E-Mail Removed)> wrote:

> I'd like return a value from a table, using Vlookup, where a number is more
> than the amount in column A and less than the amount in column B, and the
> column lookup offset is determined by referencing data in that's been
> entered in a different cell (not in the table, but in the same row as this
> formula I'd like to create). Is this possible, or is there a workaround
> for the greater than and less than columns? TIA,
>

"J.E. McGimpsey" <(E-Mail Removed)> wrote in
news:jemcgimpsey-(E-Mail Removed):

> Are you using continuous ranges in ascending order? or ranges that
> are continuous within the granularity of your data? for instance:
>
> A B
> 1 0 1.00
> 2 1.01 2.00
> 3 2.01 3.00
>
This works really well, even though the column (A in our example) jumps
by 5 each row for the first half and ten each row the second half. I'm
not sure about the differences you sighted with the not continous
example. I've had problems making sumproduct work for me with another
formula earlier in working up this sheet, even though it could have saved
me serveral extra columns and steps. The new year looms close, so I'll
take your wonderful help and run with it, then try to get my mind around
sumproduct when the dust settles. Thanks again.

Buckwheat


> If so, you only need to do a VLOOKUP() starting in column A,
> ignoring B, but using TRUE as the fourth parameter (assume J1 has
> the lookup value and J2 the column offset):
>
> =VLOOKUP(J1, A:G, J2, TRUE)
>
> Adjust the column offset as needed.
>
> If your data is not continous, but also is not overlapping, e.g.:
>
> A B
>
> 1 0 1
> 2 1.5 2.5
> 3 4 7
>
> then you can use a SUMPRODUCT():
>
> =SUMPRODUCT(--(A1:A3<J1),--(B1:B3>=J1),OFFSET(C1,0,J2,3,1))
>
>
>
> In article <Xns9454910FEDF0BBuckwheat@68.12.19.6>,
> Buckwheat <(E-Mail Removed)> wrote:
>
>> I'd like return a value from a table, using Vlookup, where a number
>> is more than the amount in column A and less than the amount in
>> column B, and the column lookup offset is determined by referencing
>> data in that's been entered in a different cell (not in the table,
>> but in the same row as this formula I'd like to create). Is this
>> possible, or is there a workaround for the greater than and less than
>> columns? TIA,
>>
>