MATCH problem

[Jan Kronsell]

I'm trying to create a spreadsheet for a colleague

I have the follwing numbers placed in Data!C4:L4
20 25 30 35 40 45 50 55 60 65

I use this formula

MATCH(B3,Data!C4:L4,1) to return the column number.

If the user enters anything between for instance 30 and 35 it returns 3 as
it is supposed to.
Unfortunately that is not what my colleague wants. He wants 30, 31, 32 to
return 3 and 33, 34 and 35 to return 4. And same for other values of course.

Is that possible at all using worksheetfunctions or do I have to create my
own UDF?

Jan

 

[Eduardo]

Hi,
try

=MATCH(CEILING(B3,4),C4:L4,1)

 

[Jan Kronsell]

Perferct, thank you.

Jan

Hi,
try

=MATCH(CEILING(B3,4),C4:L4,1)

 

[Pete_UK]

Here's one way:

=IF(B3<20,"",MATCH(B3+2,Data!C4:L4,1))

Hope this helps.

Pete