IF statement and SEARCH

[maymano]

=IF($E3="Y",IF($B3=(SEARCH($O$2,LEFT(B:B,5))),$A3,""),"")


I want to include a search function for the first 5 digits in column B and include it in an IF statement. I am not doing it right as I get a VALUE error.


Column B3 has a list of codes. I have a code in O2 (e.g.12345) and I want to only use the first 5 digits of numbers in column B (may have 123456789) and test it against o2. If there is a match in the first 5 digits of B3 to o2 then put A3 in the cell else blank etc.



Thanks.

[Paul]

Hi Maymano,

You switch between referencing a cell, B3, and all of column B (as well as column B3, which doesn't exist). Are you trying to create a formula for one cell? Or are you trying to create THIS formula and put it in cell, say, C3. You'll then copy it down to C4, C5, C6, etc.?

In your example, what if the match was in B829? Would you still want it to pull the value from A3, or from A829?

[maymano]

yes, want to copy it down so number of rows change.

if the match was in b829, i would want to use a829. I have pinned the columns but not the rows. thanks.

[brokenbiscuits]

If your numbers are in text format, you could use:

=IF($E3="Y",IF(COUNTIF(B:B,CONCATENATE(O2&"*"))>0),$A3,""),"")

?

[Paul]

This may work for you, if I'm understanding correctly:

=IF(AND($E3="Y",LEFT($B3,5)+0=$O$2),$A3,"")

The "+0" in the middle assumes the data you type into cell O2 is a number and the cell is formatted as a number. The LEFT function returns text, so if you try to compare the text string "12345" to the number 12345, you won't get a match.

If there can be text in O2 (or in column B data) you can exclude the +0.