Advance LookUp....

[ZarrinPour]

Hi all
I have a table like this in one sheet 1

A B C
--- ---- -------
10 20 1020
20 30 2030
30 40 3040
....
.......
...........

in sheet2 when a user enters a number in cell A1 ,then i want to search above records and return apprpriate result
based the following formula to the cell A2:

10>= user input >=20 --> return 1020
20>= user input >=30 --> return 2030
30>= user input >=40 --> return 3040

how can i do that with excell functions

Kind Regards.

[JBeaucaire]

You don't actually need the column B on the Sheet1 table, a lookup table only needs:
1) The column A data needs to be sorted ascending
2) The column A data represents the beginning of each range
3) The data to return in a column to the right somewhere.


So, based on that, the formula on sheet2 in cell B1 could be:

=LOOKUP(A1, Sheet1!A:A, Sheet1!C:C)

or

=VLOOKUP(A1, Sheet1!A:C, 3, TRUE)

or

=INDEX(Sheet1!C:C, MATCH(A1, MATCH(A1, Sheet1!A:A, TRUE))

[ZarrinPour]

You don't actually need the column B on the Sheet1 table, a lookup table only needs:
1) The column A data needs to be sorted ascending
2) The column A data represents the beginning of each range
3) The data to return in a column to the right somewhere.


So, based on that, the formula on sheet2 in cell B1 could be:

=LOOKUP(A1, Sheet1!A:A, Sheet1!C:C)

or

=VLOOKUP(A1, Sheet1!A:C, 3, TRUE)

or

=INDEX(Sheet1!C:C, MATCH(A1, MATCH(A1, Sheet1!A:A, TRUE))

Hi JBeaucaire
Thanks for your reply
To tell you the truth , in real situation that table may change like this

10 15 result1
42 105 result 2
...
....
so i should serach for appropriate record and then ...
now how can i handle this condition?
once again thank you for following my problem.
Regards.