Formulas look up question

[rylv5050]

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!!!

 

[Bob Phillips]

If you can organise our data like so


A1 = 210 B1 = 0
A2 = 211 B2 = 211.045645
A3 = 211 B3 = 212.098332

etc., you can use

=INDEX(Sheet2!A:A,MATCH(C1,Sheet2!B1:B50,TRUE))

 

[Mike H]

Hi,

You want to find the closest match to C1 on column A of sheet 2 and return
column B? Try this

=INDEX(Sheet2!B1:B100,MATCH(MIN(ABS(Sheet2!A1:A100-Sheet1!C1)),ABS(Sheet2!A1:A100-Sheet1!C1),0))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Ronald R. Dodge, Jr.]

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$500,-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]

OK - I tried both examples and can not get them to work. Let me provide the
actual sheet and field references since I tried plugging mine in and that
didn't work.

I am working on a sheet called xxx-xxxx, I am trying to input a formula in
M2 to evaluate K2. 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 (A5) on
sheet1 to M2.

Thank you for the previous responses but I guess their logic was over my
head or my understanding wasn't clear - thanks again!

 

[rylv5050]

Correction