#### Lookup Ranges and return value

```I have some data that looks like this...

1001399    John    Doe
1001200    Bugs   Bunny

etc.....

I have a range of data to return the department they work in...

10011000	10012999	EAG
10015000	10015999	GMS
10013024	10013036	PILM
10013000	10013015	PILM
10013048	10013060	PILM
10013300	10013599	PILM
10013100	10013100	PILM
10013122	10013122	PILM
10013600	10013621	PILM
10014612	10014612	Other
10014700	10014725	Other
10014000	10014199	PILM

Normally I would use a sumproducts to return the deparment but they
have 1000's of entry I would have to test.  So I am trying to do some
kind of lookup that tries to find the number in a range then return
the department.

Example.

If 10011111 is between 100110000 and 100012999 then return EAG, but if
not then look between 10015000 and 10015999.

Does anyone have a clue on how to do this in excell?
```
 0
5/27/2008 2:55:36 PM
excel 39879 articles. 2 followers.

4 Replies
680 Views

Similar Articles

[PageSpeed] 7

```  Col A               Col B           Col C
10011000        10012999         EAG
10015000        10015999        GMS

use =3Dlookup(d1,A:A,C:C)

i think this help u ?

wrote:
> I have some data that looks like this...
>
> 1001399 =A0 =A0John =A0 =A0Doe
> 1001200 =A0 =A0Bugs =A0 Bunny
>
> etc.....
>
> I have a range of data to return the department they work in...
>
> 10011000 =A0 =A0 =A0 =A010012999 =A0 =A0 =A0 =A0EAG
> 10015000 =A0 =A0 =A0 =A010015999 =A0 =A0 =A0 =A0GMS
> 10013024 =A0 =A0 =A0 =A010013036 =A0 =A0 =A0 =A0PILM
> 10013000 =A0 =A0 =A0 =A010013015 =A0 =A0 =A0 =A0PILM
> 10013048 =A0 =A0 =A0 =A010013060 =A0 =A0 =A0 =A0PILM
> 10013300 =A0 =A0 =A0 =A010013599 =A0 =A0 =A0 =A0PILM
> 10013100 =A0 =A0 =A0 =A010013100 =A0 =A0 =A0 =A0PILM
> 10013122 =A0 =A0 =A0 =A010013122 =A0 =A0 =A0 =A0PILM
> 10013600 =A0 =A0 =A0 =A010013621 =A0 =A0 =A0 =A0PILM
> 10014612 =A0 =A0 =A0 =A010014612 =A0 =A0 =A0 =A0Other
> 10014700 =A0 =A0 =A0 =A010014725 =A0 =A0 =A0 =A0Other
> 10014000 =A0 =A0 =A0 =A010014199 =A0 =A0 =A0 =A0PILM
>
> Normally I would use a sumproducts to return the deparment but they
> have 1000's of entry I would have to test. =A0So I am trying to do some
> kind of lookup that tries to find the number in a range then return
> the department.
>
> Example.
>
> If 10011111 is between 100110000 and 100012999 then return EAG, but if
> not then look between 10015000 and 10015999.
>
> Does anyone have a clue on how to do this in excell?

```
 0
5/27/2008 6:05:53 PM
```=VLOOKUP(Number,A2:C10000,3)

This assumes that your table is in three columns (A2:C10000), sorted by the first column in
ascending order.

HTH,
Bernie
MS Excel MVP

>I have some data that looks like this...
>
> 1001399    John    Doe
> 1001200    Bugs   Bunny
>
> etc.....
>
>
> I have a range of data to return the department they work in...
>
> 10011000 10012999 EAG
> 10015000 10015999 GMS
> 10013024 10013036 PILM
> 10013000 10013015 PILM
> 10013048 10013060 PILM
> 10013300 10013599 PILM
> 10013100 10013100 PILM
> 10013122 10013122 PILM
> 10013600 10013621 PILM
> 10014612 10014612 Other
> 10014700 10014725 Other
> 10014000 10014199 PILM
>
>
> Normally I would use a sumproducts to return the deparment but they
> have 1000's of entry I would have to test.  So I am trying to do some
> kind of lookup that tries to find the number in a range then return
> the department.
>
> Example.
>
> If 10011111 is between 100110000 and 100012999 then return EAG, but if
> not then look between 10015000 and 10015999.
>
> Does anyone have a clue on how to do this in excell?

```
 0
Bernie
5/27/2008 6:07:49 PM
```I've tried Bernie's method and it does not exactly work i.e. it  is
returning a values even if the Number is outside the range.

Are there any alternatives? If statements?

I basically have the same problem:

Col A = Number Range From
Col B = Number range To
Col C = Identifier

From	To	Identifier
5085497	5086589	EA10018
5086590	5087056	EA10019
5087057	5088369	EA10020
5752335	5758411	EA10018
5758412	5761311	EA10019
5761312	5768263	EA10020
7037820	7038912	EA10018
7038913	7039379	EA10019
7039380	7040692	EA10020
91799833	91816874	EA10018
91816875	91833041	EA10019
91833042	91844917	EA10020

I am getting a value returned i.e. 'EA10020' for numbers > 91844917.

Thanks,

Dodi

>   Col A               Col B           Col C
> 10011000        10012999         EAG
> 10015000        10015999        GMS
>
> use =lookup(d1,A:A,C:C)
>
> i think this help u ?
>
>
>
> wrote:
> > I have some data that looks like this...
> >
> > 1001399    John    Doe
> > 1001200    Bugs   Bunny
> >
> > etc.....
> >
> > I have a range of data to return the department they work in...
> >
> > 10011000        10012999        EAG
> > 10015000        10015999        GMS
> > 10013024        10013036        PILM
> > 10013000        10013015        PILM
> > 10013048        10013060        PILM
> > 10013300        10013599        PILM
> > 10013100        10013100        PILM
> > 10013122        10013122        PILM
> > 10013600        10013621        PILM
> > 10014612        10014612        Other
> > 10014700        10014725        Other
> > 10014000        10014199        PILM
> >
> > Normally I would use a sumproducts to return the deparment but they
> > have 1000's of entry I would have to test.  So I am trying to do some
> > kind of lookup that tries to find the number in a range then return
> > the department.
> >
> > Example.
> >
> > If 10011111 is between 100110000 and 100012999 then return EAG, but if
> > not then look between 10015000 and 10015999.
> >
> > Does anyone have a clue on how to do this in excell?
>
>
```
 0
Dodi (2)
6/6/2008 5:30:00 AM
```Dodi,

There are any number of ways to deal with that.  Here are two:

Put another entry into your table for numbers outside the range of values - at the bottom of the
table
91844918   <Leave Empty>  "Out of Range"

Check the number before doing the lookup:
=IF(Number > 91844917,"Out of Range",VLOOKUP(......))

HTH,
Bernie
MS Excel MVP

"Dodi" <Dodi@discussions.microsoft.com> wrote in message
>
> I've tried Bernie's method and it does not exactly work i.e. it  is
> returning a values even if the Number is outside the range.
>
> Are there any alternatives? If statements?
>
> I basically have the same problem:
>
> Col A = Number Range From
> Col B = Number range To
> Col C = Identifier
>
> From To Identifier
> 5085497 5086589 EA10018
> 5086590 5087056 EA10019
> 5087057 5088369 EA10020
> 5752335 5758411 EA10018
> 5758412 5761311 EA10019
> 5761312 5768263 EA10020
> 7037820 7038912 EA10018
> 7038913 7039379 EA10019
> 7039380 7040692 EA10020
> 91799833 91816874 EA10018
> 91816875 91833041 EA10019
> 91833042 91844917 EA10020
>
> I am getting a value returned i.e. 'EA10020' for numbers > 91844917.
>
> Thanks,
>
> Dodi
>
>
>
>>   Col A               Col B           Col C
>> 10011000        10012999         EAG
>> 10015000        10015999        GMS
>>
>> use =lookup(d1,A:A,C:C)
>>
>> i think this help u ?
>>
>>
>>
>> wrote:
>> > I have some data that looks like this...
>> >
>> > 1001399    John    Doe
>> > 1001200    Bugs   Bunny
>> >
>> > etc.....
>> >
>> > I have a range of data to return the department they work in...
>> >
>> > 10011000        10012999        EAG
>> > 10015000        10015999        GMS
>> > 10013024        10013036        PILM
>> > 10013000        10013015        PILM
>> > 10013048        10013060        PILM
>> > 10013300        10013599        PILM
>> > 10013100        10013100        PILM
>> > 10013122        10013122        PILM
>> > 10013600        10013621        PILM
>> > 10014612        10014612        Other
>> > 10014700        10014725        Other
>> > 10014000        10014199        PILM
>> >
>> > Normally I would use a sumproducts to return the deparment but they
>> > have 1000's of entry I would have to test.  So I am trying to do some
>> > kind of lookup that tries to find the number in a range then return
>> > the department.
>> >
>> > Example.
>> >
>> > If 10011111 is between 100110000 and 100012999 then return EAG, but if
>> > not then look between 10015000 and 10015999.
>> >
>> > Does anyone have a clue on how to do this in excell?
>>
>>

```
 0
Bernie
6/6/2008 1:01:33 PM

Similar Artilces:

Identifying the top five values in multiple groups
I've got a spreadsheet of pay information for about eight hundred people. Each person is on one of eight salary scales I'd like to create a new worksheet that shows the details of just the 5 highest paid people in each scale (name, dept, salary, etc.) - and also the five lowest. Ideally, I'd like also to be able to vary that number - eg the top ten, the highest, etc.. Can someone help? Thanks Suppose you have data in Sheet1 in the below format Col A Col B Col C Name Scale Salary a1 1 101 a2 1 102 a3 1 103 a4 2 104 In Sheet2 cel...

How to remove Cartages returns?
How do you remove Cartages returns when import excel to Access? On Wed, 24 Oct 2007 08:18:04 -0700, Hortp01 wrote: > How do you remove Cartages returns when import excel to Access? And replace it with what? = Replace([FieldName],chr(10),"") -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail With a space. On Wed, 24 Oct 2007 09:11:03 -0700, Hortp01 wrote: > With a space. It's polite to include the relevant portion of any previous message in a new message. Without a reference to your previous message and my reply, any other reader will h...

I have this code in an event macro: If Target.Address = "\$BC\$3" Or Target.Address = "\$BC\$4" Or Target.Address = "\$BC\$5" Or Target.Address = "\$BC\$6" Or Target.Address = "\$BC\$7" Then Is there a more compact way to write that statement? Thanks for any help on this. Hi Look at this: Set isect = Intersect(Target, Range("BC3:BC7")) If Not isect Is Nothing Then or just: If Not Intersect(Target, Range("BC3:BC7")) Is Nothing Then Regards, Per "dhstein" <dhstein@discussions.microsoft.com...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Dynamic Range #2
Hello, I am working on some VB for spreadsheet. Is there a way to make the code to dynamically recognize the last cell with data in it? I currently have my code hard coded (as below) and I wanted to make the "G222" part dynamic to find the last cellw ith data in it. Worksheets("Sheet1").Range("A3:G222").Sort _ Thank you very much in advance. assumes a is your LONG row. Don't forget the . s dim lr as long with Worksheets("Sheet1") .lr=.cells(rows.count,"a").end(xlup).row .Range("A3:G" & lr).Sort end with -- D...

return receipt #4
OL 2002 SP3 Win XP HE SP1 Follow-up to: microsoft.public.outlook If I send an email requesting a return receipt, when the email is clicked or opened, it will pop-up on recipient's work station asking if he wants to send return receipt. Let's say he says no. But obviously reads the email anyway. When I get the return receipt back, it will say "email was deleted without being read." Can this mean that the above happened, i.e. recipient clicked No on sending return receipt or does it mean that recipient definitely deleted the email based on seeing its header in his inbox...

Problem with Null value elimintation criteria
Access 2007 on Vista. I'm building a simple append query to add missing records to a table. It examines a list of entries, identifies which are not in the destination table, and adds them. Simple thus far. The problem comes when I add a criteria to the source side to ensure no blank entries are appended. Here's the SQL I'm trying to use: INSERT INTO tblAgents ( AgentID, AgentName ) SELECT qryAgentsImport.RecAgentID, First(qryAgentsImport.RecAgentName) AS FirstOfRecAgentName FROM tblAgents RIGHT JOIN qryAgentsImport ON tblAgents.AgentID = qryAgent...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Handling #REF returned from GETPIVOTDATA
A GETPIVOTDATA function returns #REF, but when I change that formula to =ISREF(GETPIVOTDATA... I get FALSE. I would have expected TRUE. Why the difference This is part of the development of a formula to return a zero when GETPIVOTDATA returns #REF. Thanks Laurence Lombard Try =if(iserror(getpivotdata(.... =isref() will return true if you pass it an address: =isref(a1) (since A1 is an address) I don't recall ever seeing this used on a worksheet. I bet that this may have been useful in the older XLM style macros (just a guess). Laurence Lombard wrote: > > A GETPIVOTDATA fu...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Entering an unbound form control value into a new record field.
Hello. I hope this (friendly and helpful) group will forgive a very noob question, but I learned from my (long gone) self-teaching years with foxpro that a little good advice can save a lot of grief. I've been setting up my tables for first time Access project (2007 on Vista), and thinking about my first form. It will have a couple subforms in it, and is intended for browsing as well as entering a new record. My focus at moment is on a text box, whose value will be derived (and therefore be unbound) from other bound controls (an autonumbered integer (the PK), a user enter...

Print Range Lines
Is there any way to turn off the dotted lines that show the print range area within worksheets? I don't know why someone would want them unavailable, but a co-worker of mine insists "they don't turn on half of the time anyway, so why use/show them at all?" We use Excel 2003. Thanks. Tools>options>view and under window options deselect page breaks -- Regards, Peo Sjoblom "Me" <Me@discussions.microsoft.com> wrote in message news:B194547E-DE11-4508-97E2-52F99AF9F096@microsoft.com... > Is there any way to turn off the dotted lines that show ...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

sort dynamic range on active cell
Hi there is there anyway to sort a dynamic range (ie amount of columns and rows may vary) based on the column of the active cell. to make things even more complicated i would like to have a button that when you click it the data is sorted ascending and if you click it again it is descending) Examle: Name, age, class, height = col headings if i click a in any cell in col b (age) then click button it sorts data ascending, click again and its descending If i then select a cell in col d (height) i can sort that col based on active cell being in it i hope there is a neat way ...

I would like to log in one of the databases the username of the person who is making changes to a record in a database, but I noticed that access 2207 doesn't have user level security anymore and the currentuser method just returns admin. Is there a way to do this? On Jan 3, 10:16 am, icccapital <icccapi...@discussions.microsoft.com> wrote: > I would like to log in one of the databases the username of the person who is > making changes to a record in a database, but I noticed that access 2207 > doesn't have user level security anymore and the currentuser method ju...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Selecting a range of cells based on the date.
Hi MS Office Help, I would like to know how to choose a selection of cells based on what month it is. For instance, if the month was january, excel would allow me to select cells A1-F1, which I could then use as a data source for a chart. If the month became march or april, excel would allow me to select cells C1-H1, D1-I1 respectively, and so on. This will allow me to vary my chart automatically as the month changes. Help is greatly appreciated. Hi Solomon, Use the Data - Autofilter -custom feature or Data - filter - text filters - custom depending on your version of...

Colour fill within a range
Hi I have a spreadsheet that i want people to be able to colour cells in using a macro I also only want them to colour specific cells. A3 to Z25 The sheet is protected ,and to enable them to colour cells i have a macro which first unprotects- colours the active cell - reprotects. is there any way that i can prevent people from colouring cells that they are not supposed to.(e.g A1) I Only want people to colour cells between Column A Row 3 and Column Z row 25. This is the macro: Sub Macro1() On Error Resume Next ThisWorkbook.Worksheets("paint").Unprotect ThisWorkbook.Worksheets(&...

Blank Repeated Values
I have a list in Column A that displays multiple data in an unfilled manner. I have a list in Column B that displays multiple data in a filled manner. How do I autofill the data points in Column A? Example: A1=1 A2:A9=(blank) A10=2 A11:A19=(blank) B1:B9=Blue B10:B19=Red I do not want to manually autofill each data break because there are 30,000+ rows of data (A1:A30000) with data points starting at 1(A1) to 15,000(A29999). The format looks like a finished pivot table. It looks like I am trying to copy a finished pivot table and pasting value to another sheet, then running a pivo...