Search interval

  • Thread starter Thread starter jkrons
  • Start date Start date
J

jkrons

I have the following structure:

A B C
1000 1200 01-30-09
1201 1456 01-31-09
1457 1603 02-01-09

If I type 1179 in celle D1, I would like 01-30-09 returned in D1.
If I type 1500 in celle D1, I would like 02-01-09 returned in D1. and
so on.

Can this be done using a formula, or do I have to create a UDF?

Jan
 
You'd need a macro to do that.

You could do it with this formula in E1:

=VLOOKUP(D1,A1:C3,2)

if you don't mind using another cell.

Hope this helps.

Pete
 
I have the following structure:

A B C
1000 1200 01-30-09
1201 1456 01-31-09
1457 1603 02-01-09

If I type 1179 in celle D1, I would like 01-30-09 returned in D1.
If I type 1500 in celle D1, I would like 02-01-09 returned in D1. and
so on.

Can this be done using a formula, or do I have to create a UDF?

Jan
Click to expand...

If you type a value into a cell, you will destroy any formula that is present
in that cell.

If you have a formula in a different cell, it can only return a value to that
different cell; it cannot affect directly the contents of the cell into which
you are typing.

So, although the VLOOKUP worksheet function will return the desired answer,
there is no way, using a function (worksheet or user defined) to have that
result replace what you've typed into D1.

You could use a VBA worksheet event procedure to do something like this. Or
you could use different cells for your data input and results. The result cell
would contain the VLOOKUP formula.

Which do you want to do?
--ron
 
My mistake. I type in D1 and have the result in E1.

The VLOOKUP function will work in most cases.

Jan
 
My mistake. I type in D1 and have the result in E1.

The VLOOKUP function will work in most cases.

Jan
Click to expand...

VLOOKUP should work then. You write it "... will work in most cases". I would
expect it to work in all cases. In what cases does it not work?
--ron
 
It only works when the numbers at continuous, which they
unfortnunately not always are.
i created a UDF, that solved the problem.

Function FInterval(cel As Long, rn As Range, kol As Byte) As Variant

For Each c In rn.Columns(1).Cells
If cel >= c.Value And cel <= c.Offset(0, 1).Value Then
FInterval = Format(c.Offset(0, kol - 1).Value, "dd-mm-yy")
Exit Function
End If
Next c
FInterval = CVErr(xlErrNA)
End Function

Jan
 
Just to explain. Using real world data, the list could look like

1000 1107 01-30-09
1200 1345 01-31 09
1456 1500 02-01-09

in wich case VLOOKUP will return a wrong date if I try to find 1400.

Jan
 
Just to explain. Using real world data, the list could look like

1000 1107 01-30-09
1200 1345 01-31 09
1456 1500 02-01-09

in wich case VLOOKUP will return a wrong date if I try to find 1400.

Jan
Click to expand...

This formula may do it:

=IF(A1<=VLOOKUP(A1,Tbl,2),VLOOKUP(A1,Tbl,3),NA())

--ron
 
Back
Top