# Formatting Problem in Formula

#### Vishaal

##### Active Member
Hi All,

We have used a formula in column G, but when we check the Cell Format of any E5:F16 its coming

but when we check the cell format of G5:G16 it is coming the following

Problem is how can we get the Cell Format setting as per first image in column G

please find the excel file also.

xlsx file as u have told.xlsx
EFG
4AmountBatchTesting
53000
63200
73000,3200,85008500
84000
92000
104000,2000,30003000Flag
117654
123000,8000,40004000Flag
136000
144000,6000,30003000Flag
155000
166000Flag
Sheet1
Cell Formulas
RangeFormula
G5:G16G5=IF(COUNTIF(\$G\$4:G4,LOOKUP(2,1/SEARCH(F5,\$E\$4:E4),ROW(\$E\$4:E4)))>0,0,IFERROR(LOOKUP(2,1/SEARCH(F5,\$E\$4:E4),ROW(\$E\$4:E4)),""))

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### denzo36

##### Board Regular
Hi there, could you explain what your formula is trying to achieve?

#### Vishaal

##### Active Member
Yes its trying to find the E7 three value in column F but from the F8 (any one amount) and after that it will stop working and again we will put the new value in column E and it will start the doing same thing again

#### Fluff

##### MrExcel MVP, Moderator
Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?

#### Vishaal

##### Active Member

thanks Fluff ji,

may be it will work in my case,

by the way my main problem is -
we are getting the Flag but when we try to put any formula in Column E10 its not taking is a Flag, you can try the following code in E10
VBA Code:
``=IF(OR(\$B10=3,\$G10="Flag"),Get4Numbers(\$F\$6:\$F10),"")``

Do you mean that rather than getting the word "flag" in col G, you want to see the number from the formula?

#### Fluff

##### MrExcel MVP, Moderator
I can't try that formula, because it uses a UDF, which I don't have.

#### Vishaal

##### Active Member

Pls find the UDF

Code:
``````Function Get3Numbers(Batch As Range)

Dim T As Integer, X As Integer
Get3Numbers = ","

For T = 1 To WorksheetFunction.Min(3, Batch.Rows.Count)
If IsNumeric(Batch.Cells(Batch.Rows.Count - X, 1)) Then

If InStr(1, Get3Numbers, "," & Batch.Cells(Batch.Rows.Count - X, 1) & ",") = 0 Then
Get3Numbers = "," & Batch.Cells(Batch.Rows.Count - X, 1) & Get3Numbers
Else
T = T - 1
End If

End If
X = X + 1

Next T

If Get3Numbers <> "" Then Get3Numbers = Mid(Left(Get3Numbers, Len(Get3Numbers) - 1), 2)

End Function``````

I can't try that formula, because it uses a UDF, which I don't have.

#### Fluff

##### MrExcel MVP, Moderator
The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number

#### Vishaal

##### Active Member
You are right, what can we do that in that case, we need any text

The reason it doesn't work is that column G does not contain the word "Flag" it contains either "" or a number

#### Fluff

##### MrExcel MVP, Moderator
Maybe
=IF(OR(\$B10=3,\$G10<>""),Get4Numbers(\$F\$6:\$F10),"")

Replies
5
Views
84
Replies
7
Views
53
Replies
2
Views
83
Replies
2
Views
87
Replies
3
Views
137