Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a number within a range of numbers | Excel Worksheet Functions | |||
finding a number within a range | Excel Worksheet Functions | |||
FInding the largest number in a range meeting a criteria | Excel Discussion (Misc queries) | |||
Finding 2nd smallest number in range | Excel Worksheet Functions | |||
Finding Number Within Range Then Copying Data Below Number to Cells | Excel Programming |