Search on a Series/Range of Numbers

[alchohaz]

I have a table of information containinga series of numbers that relate directly to an identifier (Site) - probably easier to give a practical example:

So i have a range of Numbers from 40000 - 42999 and this is associated with a location - i.e. London

I have another range 43000 - 44999 and this is associated with s different location - i.e. Birmingham

On the sheet it is presented as ONE row per site as follows:

Table 01
Extension Range		Site
40000 - 42999		  London
43000 - 44999		  Birmingham

This is the table that i want to do a lookup on.

I have another table that lists the individual Extension numbers For example 40001, 40002, 40007 etc etc,

i would like to be able to perform a lookup of the extension numbers against the first table to establish which site the number exists.

I am pretty happy with VLOOKUP and beleive this is the area i should be looking in, but obviously the numbers i have listed in Table 01 will not match as 40001 etc is not shown up in it - can someone please assist or point me the right direction


Thank you !!

 

[Gruuuu]

Add another column.
Put this formula in it
=VALUE(LEFT(A1,SEARCH(" ",A1)))
Or
=VALUE(RIGHT(A1,LEN(A1)-2-SEARCH(" ",A1)))

Depending on how the Vlookup guesses the next value. I don't remember.

 

[SkipVought]

Hi,

This is a simple spreadsheet problem -- NO VBA REQUIRED.

Your table...
[tt]
Extension Range Site
0 Some other
40000 London
43000 Birmingham
45000 More other
[/tt]
Your formula where D2 has a lookup value and using Named ranges...
[tt]
=INDEX(Site,MATCH(D2,Extension_Range,1),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alchohaz]

Thank you both, i went for the following and it did the Job for me...

=INDEX(DDI_TAB1,MATCH(B2,Ext_Srch,1),6)

Cheers
Al