Finding a number within a range in Excel
 

Hello,

My name is April.

I am attempting to find a result when Excel searches within a range.
For example, the User will input the FILE NO, and hit enter,
thereafter, Excel will search columns A & B (see below) and come up
with the correct result in Column C.

The only way I could think of accomplishing this is using the IF, AND
funtion:

=IF(AND(B1=A4,B1<=B4),C4,"CAN'T FIND")

If B1 is greater and equal to A4 or less than B4, then return C4, if
not return CAN'T FIND.

This works, but only for row that specified cells.

I am needing to search an entire range to get the "Shelf Location" and
keep in mind the user will use a number that will be between the Begin
File No and End File No. SEE EXAMPLE BELOW:


FILE NO: (USER ENTERS ABC-345)
LOCATION RESULT: Shelf 2
(COL. A) (COL. B) (COL. C)
*BEGIN FILE NO.* *END FILE NO.* *SHELF LOCATION*
ABC-103 ABC-258 SHELF 1
ABC-259 ABC-350 SHELF 2
DEF-100 DEF-125 SHELF 3
DEF-126 DEF-150 SHELF 4

OBJECTIVE per the above example is to have Excel bring back result
from column C (Shelf Location) to place in cell after "Location
Result" which will be in this case -- "Shelf 2", since ABC-345 is
between (Begin File No) ABC-259 and (End File No.) ABC-350.

PLEASE HELP, THANKS!!!!

Finding a number within a range in Excel
 

On Mar 28, 2:56 pm, wrote:
Hello,

My name is April.

I am attempting to find a result when Excel searches within a range.
For example, the User will input the FILE NO, and hit enter,
thereafter, Excel will search columns A & B (see below) and come up
with the correct result in Column C.

The only way I could think of accomplishing this is using the IF, AND
funtion:

=IF(AND(B1=A4,B1<=B4),C4,"CAN'T FIND")

If B1 is greater and equal to A4 or less than B4, then return C4, if
not return CAN'T FIND.

This works, but only for row that specified cells.

I am needing to search an entire range to get the "Shelf Location" and
keep in mind the user will use a number that will be between the Begin
File No and End File No. SEE EXAMPLE BELOW:

FILE NO: (USER ENTERS ABC-345)
LOCATION RESULT: Shelf 2
(COL. A) (COL. B) (COL. C)
*BEGIN FILE NO.* *END FILE NO.* *SHELF LOCATION*
ABC-103 ABC-258 SHELF 1
ABC-259 ABC-350 SHELF 2
DEF-100 DEF-125 SHELF 3
DEF-126 DEF-150 SHELF 4

OBJECTIVE per the above example is to have Excel bring back result
from column C (Shelf Location) to place in cell after "Location
Result" which will be in this case -- "Shelf 2", since ABC-345 is
between (Begin File No) ABC-259 and (End File No.) ABC-350.

PLEASE HELP, THANKS!!!!
This formula assumes the file number is in A1, and the data(begin file
no., end file no., and shelf location) is in A3:C5
=INDEX(C3:C5,MATCH(A1,A3:A5,1))

You can take a look at this thread:
http://groups.google.com/group/micro...e373efcd47a043
which discussed a similar problem.
I hope that helps, unfortunately, I didn't have time to extensively
check the formula, but I think it will work ok.