IF Look Up Formula

[rylv5050]

I hope this question comes through a little clearer than my last attempt. I
will reference the actual sheet and cell numbers so I can see what the actual
formula will look like.

I am working on a sheet called xxx-xxxx, I am trying to input a formula in
M2 to evaluate K2. K2 is numeric entry such as 123.12 (3-4 digits before
decimal and 2 digits to the right). I would like the formula to search
column B on
sheet1(there are up to 5 decimals and no whole numbers in each instance) -
once a cell in column b has been identified (i.e. K2 is > B5 and less than B6
so I want to identify B5) I want to return the value adjacent to B5 (the one
in A5) back to xxx-xxxx M2.

 

[Ronald R. Dodge, Jr.]

This is where the Match function comes into play. However, for this to
work, you must have column B sorted in ascending order for the Match
function to work properly.

Cell M2 on worksheet "xxx-xxxx" will contain the following formula:

=IF(ISERROR(MATCH($K2,Sheet1!$B:$B,1)),0,INDIRECT(ADDRESS(MATCH($K2,Sheet1!$B:$B,1),COLUMN(Sheet1!A:A),,,"Sheet1")))

This does assume that the value in xxx-xxxx!K2 is in the same format as the
values in Sheet1!B:B else the MATCH function will return an #NA! error
message, thus cause the above formula to return the value of '0'.
--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000