Lookup Ranges and return value

[caradomski]

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?

 

[muddan madhu]

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

use =lookup(d1,A:A,C:C)

i think this help u ?

 

[Bernie Deitrick]

=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

 

[Dodi]

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

 

[Bernie Deitrick]

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