Choose From a Range

[Guest]

Dear all,

I've data in a table as follows:

Starting # Ending # Cabinet # Column # Row #

12345 45678 A 1 1
51000 65000 A 1 2
70000 78000 A 1 3

In the above table, if I type a number, say 60000 in a free cell, it returns
the result is in "Cabinet A, Column 1 and Row 2".

Please advise what formula I should put in the free cell so that i can get
my expected result? (I'm not sure VLOOK can look for a result in ranges)

Thanks in advance.

 

[Max]

As it stands, here's one play to try ..

Assuming the source table below is in Sheet1, A1:E4

Starting # Ending # Cabinet # Column # Row #
12345 45678 A 1 1
51000 65000 A 1 2
70000 78000 A 1 3

In Sheet2
---------
Assume numbers will be input in A1 down

Put in B1:

=IF($A1="","",IF($A1<MIN(Sheet1!$A:$A),"No such number
issued",IF(ISNUMBER(MATCH($A1,Sheet1!$B:$B,0)),INDEX(Sheet1!C:C,MATCH($A1,Sh
eet1!$B:$B,0)),IF(ISNUMBER(MATCH($A1,Sheet1!$A:$A,0)),INDEX(Sheet1!C:C,MATCH
($A1,Sheet1!$A:$A,0)),IF(MATCH($A1,Sheet1!$A:$A,1)-1=MATCH($A1,Sheet1!$B:$B,
1),INDEX(Sheet1!C:C,MATCH($A1,Sheet1!$A:$A,1)),"No such number issued")))))

Copy B1 across to D1, fill down as far as required
(can copy ahead of data input in col A)

Cols B to D will retrieve the corresponding data from cols C to E in Sheet1.
If the number input in col A doesn't fall within any of the ranges in
Sheet1, you'd get the return: "No such number issued"

 

[Roger Govier]

And what is your expected result?
It seems fine to me. Assuming your data all lies within the range A1:E4 ,
the cell in which you enter your 60000 is cell G1 and that your formula is
=VLOOKUP(G1,$B$2:$E$5,2)
As there is no value which is 60000, VLOOKUP returns data from the highest
value found which is less than your desired value.

For the purposes of your excercise you do not need starting number just
ending number and the table should look like
Ending # Cabinet # Column # Row #

0 A 1 1
45678 A 1 2
65000 A 1 3
78000 A 1 3

Then any number between 0 and 45678 would return A 1 1, between 45679 and
65000 would return A 1 2, 65001 to 78000 would return A 1 3 and anything
above 78000 would also return A 1 3 or perhaps you want A 1 4.

Hope this helps.

 

[Max]

Here's a link to a sample file with the implemented construct:
http://www.savefile.com/files/1450137
File: Choose From a Range_Freshman_wksht.xls