Using Lookup with GT and LT Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

billbrandi - 25 May 2008 21:39 GMT

I have an array with 3 columns; (1) column A is labeled less than or equal to
and contains the lower range of values; (2) column B is labeled greater than
or equal to and contains the upper range of values; (3) column C is a
discrete value. There are over 500 rows in my array in a continuous
sequence. Example, row 1 is 0,10,[value], row 2 is 11,20,[value, row 3 is
21, 30,[value].

I want to do a lookup on the array using a separate file that has 2 columns.
Column A has a value to match with the range in each row of the array and
column B has a value to be returned.

How would I do this in Excel? I am familiar with a 2 column vlookup but
have never tried it with ranges.

Thanks
Bill

Reply to this Message

ShaneDevenshire - 25 May 2008 22:09 GMT

Hi Bill,

It doesn't sound like you need a two column VLOOKUP, you just need to do an
approximate match rather than an exact match:

=VLOOKUP(A1,[Book2]Sheet1!$A$2:$C$13,2,TRUE)

You are looking up the value in cell A1 in the range A2:A13 of the second
workbook, where that column is sorted in ascending order. You are bringing
back the item in column 2 of that range. TRUE means approximate. You can
also use 1 instead of TRUE or you can even leave the argument out:

=VLOOKUP(A1,[Book2]Sheet1!$A$2:$C$13,2)

Signature

Cheers,
Shane Devenshire
Microsoft Excel MVP

> I have an array with 3 columns; (1) column A is labeled less than or equal to
> and contains the lower range of values; (2) column B is labeled greater than
[quoted text clipped - 12 lines]
> Thanks
> Bill

Reply to this Message