Formatting Problem in Formula

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

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

Untitled.png


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

Untitled2.png


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)),""))
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Thanks Fluff ji, its working for me
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,156
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi Fluff, thanks for your help, can we use any other formula in G instead of

HTML:
Cell Formulas
Range    Formula
G5:G16    G5    =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)),"")

so that get any text, if possible
if its easy, pls check else drop it.

You're welcome & thanks for the feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,156
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Cross posted please check, where is the problem so that not getting result in column D

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,156
Office Version
  1. 365
Platform
  1. Windows
For your new question, you will need to start a new thread
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
OK

Cross posted please check, where is the problem so that not getting result in column D

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,867
Members
410,711
Latest member
Josh324
Top