I have used the following functions to do what you are looking for:

MATCH(<Value,<Single Column or Single Row,<Match Type)

First argument is the value you are looking for

Second argument can only be either a single column or a single row that you
want the function to look within the range for that particular value.

Third argument is the type of match you are using

<Match Type = -1
List must be in ascending order and looks for the largest value that is
less than or equal to <Value

<Match Type = 0
Looks for <Value within the list, but if not found, function returns
"#NA!" error message

<Match Type = 1
List must be in descending order and looks for the smallest value that
is greater than or equal to <Value

Remark

This function returns the Nth spot in the list where it located the value at
if it is found. Therefore, if you have the second argument as B15:B84, and
it had found the <Value in B32, the MATCH function will return a value of
18.

ISERROR(<Criteria)

This is to trap for those items that returns an error message so as the
formula can keep doing what it needs to do.

If(<Criteria,<True,<False)

This is to help with the error trapping and not cause issues with other
formulas dependent on this cell.

ADDRESS(<Row,<Column,,,<SheetName)

This function returns the address by default in absolute reference with it
in A1 style.

INDIRECT(<AddressReference)

This returns the value that is within the Address Reference cell.

ROW(<AddressReference)

Returns the first row number within the address reference

COLUMN(<AddressReference)

Returns the first column number within the address reference

Now for an example of putting this all together.

If the list is within B5:B500 to look up on Sheet1 and the value is on cell
C5 on Sheet2, which you want to return such value in column A of Sheet1 into
cell D5 on Sheet2, then use the following formula:

=IF(ISERROR(MATCH($D5,Sheet1!$B$5:$B$500,-1)),"",INDIRECT(ADDRESS(MATCH($D5,Sheet1!$B$5:$B$5 00,-1)
+ ROW(Sheet1!$B$5:$B$500) - 1,COLUMN(Sheet1!A:A),,,"Sheet1")))

Why did I use the ROW() and COLUMN() functions instead of absolute numbers?

Simple. If you go to insert/delete rows/columns/cells, in most cases, the
formulas should be able to adjust themselves, but if we used absolute
numbers instead such as a "1" in place of "COLUMN(Sheet1!A:A), the formulas
will not adjust and that's where it can be rather time consuming and
overlooking can take place if there's enough of such formulas in place.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"rylv5050" wrote in message
...
I am sure someone can assist with this basic formula question but I am
unable
to get it to work correctly.

I want to create a formula that will reference another sheet to do the
following:
Sheet 2
A1 = 210 B1=211.045645
A2 = 211 B2=212.098332
A3 = 212 B3=213.123123

Sheet 1
C1=210

Sheet D1 formula - (if value of c1 is < sheet 2 B1 return value in sheet2
A1)(if value is B1 but <b2 = A2 and so on and so forth) I want this
formula
to continue looking up for the B value that is closest to my # in question
and return the value in the adjacent A column. I have approximately 250
individual comparison fields to look up so I am hoping to have a formula
that
will accomplish this without being too cumbersome.

I appreciate it!!!