MATCH problem
 

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

MATCH problem
 

Hi,
try

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

"Jan Kronsell" wrote:

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


.

MATCH problem
 

Perferct, thank you.

Jan

Eduardo wrote:
Hi,
try

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

"Jan Kronsell" wrote:

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


.

MATCH problem
 

Here's one way:

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

Hope this helps.

Pete

On Oct 29, 11:16*am, "Jan Kronsell"
wrote:
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