Proper syntax for IF Function

[PhysiqologyInc]

I am trying to create the proper syntax to use the IF function for the following scenario. Cell B5 accepts a four-digit input value that I assign. This value is then broken up into four separate components in cells G5 to J5 using the VLOOKUP command to access a table I created on a separate sheet.

I need cell B6 to display the same value as B5, and cells G6 to J6 to display the components of B6, unless I decide that B6 needs to be assigned a different data point, in which case I need G6 to J6 to use the VLOOKUP function to break up the unique data entry point that I assigned and to display its components. Moreover, if there is no value assigned to B6, either because I did not assign a value to B5, or because I assigned a value to B5, but do not want to assign a value to B6 (and thus deleted the formula for B6), I need to make sure that G6 to J6 returns a null value, and remains blank.

My coding is:

B5 is assigned the value 4010.
G5 is =IF($B5>0, (VLOOKUP($B5,Tempos!$A$2:$E$103,2,0)), (" ")), which yields the value "4".
H5 is =IF($B5>0, (VLOOKUP($B5,Tempos!$A$2:$E$103,3,0)), ("")), which yields the value "0".
I5 is =IF($B5>0, (VLOOKUP($B5,Tempos!$A$2:$E$103,4,0)), ("")), which yields the value "1".
J5 is =IF($B5>0, (VLOOKUP($B5,Tempos!$A$2:$E$103,5,0)), ("")), which yields the value "0".

I have been trying to use the following formula for B6: =IF($B5>0, (B5), (" "))
That formula works for B6 itself, but causes an error for cells G6 to J6 (#N/A), which all use the same formulas as above, but reference $B6, rather than $B5. My syntax obviously messes up the VLOOKUP command of those cells, but I have no idea what or why. Any help is always appreciated.

 

[FranzV]

If you want to break a number into its digits, you could use the MID() function instead of having to do lookups. It is a cleaner approach and you won't have to worry about the lookup table having new values, etc. As for the formula in B6, you were on the right track but I would suggest you use an empty string ("") instead of a space(" "). Check out the dummy table I made using both 4010 (row 6) and an empty cell (row 8). The empty cell will make the other cells referencing it become empty.

I hope it helps.

Values

	B	C	D	E	F	G	H	I	J
5​	4,010​					4	0	1	0
6​	4,010​					4	0	1	0
7​
8​
9​

<tbody>
</tbody>

Formulas

B C D E F G H I J 5​ 4,010​ =MID($B5,1,1) =MID($B5,2,1) =MID($B5,3,1) =MID($B5,4,1) 6​ =IF(B5="","",B5)​ =MID($B6,1,1) =MID($B6,2,1) =MID($B6,3,1) =MID($B6,4,1) 7​ 8​ =MID($B8,1,1) =MID($B8,2,1) =MID($B8,3,1) =MID($B8,4,1) 9​ =IF(B8="","",B8) =MID($B9,1,1) =MID($B9,2,1) =MID($B9,3,1) =MID($B9,4,1) <tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

If you want to break a number into its digits, you could use the MID() function instead of having to do lookups. It is a cleaner approach and you won't have to worry about the lookup table having new values, etc.Formulas
[/SIZE]

B C D E F G H I J 5​ 4,010​ =MID($B5,1,1) =MID($B5,2,1) =MID($B5,3,1) =MID($B5,4,1) 6​ =IF(B5="","",B5)​ =MID($B6,1,1) =MID($B6,2,1) =MID($B6,3,1) =MID($B6,4,1) 7​ 8​ =MID($B8,1,1) =MID($B8,2,1) =MID($B8,3,1) =MID($B8,4,1) 9​ =IF(B8="","",B8) =MID($B9,1,1) =MID($B9,2,1) =MID($B9,3,1) =MID($B9,4,1) <tbody> </tbody>

<tbody>
</tbody>

You only actually have to create one formula for (given the above layout) cell G5 which can then be copied across to cell J5 and then those four cells can be copied down to the end of the data. Here is the formula for cell G5...

=MID($B5,COLUMNS($G:G),1)

 

[PhysiqologyInc]

Gentlemen,

Thank you both for the help with this challenge. This particular issue has boondoggled me for days, so its really appreciated. Thanks again.