Compare a Cell Value with first numbers in another cell.

[hoventim]

Looking to compare a changing user chosen value in one cell - to the first number(s?) of another cell.

Example file included.

Thank you, T.

[dilipandey]

Hi hoventim,

Try using below formula:-

Please Login or Register  to view this content.

See attached:- Head Example.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[hoventim]

Hi Dilipandey and thank you,

Using your example file if I input 11-19, (but not 20?), 21, or 101 into cell M2 I get unexpected results.

I works with some values in cell M2, but not these and some others?

[dilipandey]

Hi Hoventim,

It will not work for 11-19 but it should work for 11,12,13,14,15,16,17,18,19 as formula is searching first number from column A in cell M2 and if found it is returning "2" in I2 .. hope this clarifies. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[hoventim]

Here is what I eneded up with. I believe my problem was Collum 2 looked like numbers but were in fact a text string. Therefore I added "*1" to make it a number.

And/or another problem I think i was having with Dilipandey's formula was sometiimes the numbers were one digit and sometimes they were two digit.

So here it is, assuming A2 is the cell you are searching thru, and cell $M$2 contains the number you are searching for.

=IF((LEFT( A2, FIND( " ", A2 ) - 1 )*1)=$M$2,"yes,"no")

Great Excel Forum I've found much info! SOLVED !!