Index, match, & if

[ed.ayers315]

Hello forum,

I have a large amount of information that I am using index, match to lookup what I need to combine. In between these lookups, I have char(10) to make it easier to read and review.

My problem comes when the lookups do not return anything the char(10) still apply which make the cells large enough for all that dead space.

I tried using if before the index and match but cannot get it to work. It is probably because there is so much going on my eyes can't see what I am doing wrong. So I tried breaking it up into levels and then putting the formula back together.

I also tried using a helper cell and the "Clean" function but that removes everything and takes along time to fix.

I index buy colume "B" and use "&" and the letters A,B,C, or D to move to the correct row with column L as the incrementor.
<!-- ######### Start Created Html Code To Copy ########## -->SURVEY_BREAKDOWN


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Times New Roman,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 71px"><COL style="WIDTH: 348px"><COL style="WIDTH: 71px"><COL style="WIDTH: 79px"><COL style="WIDTH: 91px"><COL style="WIDTH: 73px"><COL style="WIDTH: 71px"><COL style="WIDTH: 73px"><COL style="WIDTH: 91px"><COL style="WIDTH: 155px"><COL style="WIDTH: 78px"><COL style="WIDTH: 195px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 102px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">REF NO.</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">CLEANING FUNCTION</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">PROD REF</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">OZS/GAL</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">GALS OF SOLUTION</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">CYCLES PER DAY</TD><TD style="BACKGROUND-COLOR: #ff0000; COLOR: #ffffff; FONT-WEIGHT: bold">PROD DAYS PER WEEK</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">SEASONAL ITEMS "PERIODS PER YEAR"</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">DEPT</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">EQUIP</TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Helv; COLOR: #ffffff; FONT-WEIGHT: bold">STEP
INCREMENTER</TD><TD style="BACKGROUND-COLOR: #ff0000; COLOR: #ffffff; FONT-WEIGHT: bold">CLEANING FUNCTION2</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">337</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">83</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv; COLOR: #333399; FONT-SIZE: 8pt; FONT-WEIGHT: bold">REVERSE OSMOSIS: 48% Caustic Wash</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">0.0002</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">256.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">3.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv">12 </TD><TD style="FONT-FAMILY: Helv">WHEY</TD><TD style="FONT-FAMILY: Helv">REVERSE OSMOSIS</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv">A</TD><TD style="FONT-FAMILY: Helv">48% Caustic Wash</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">338</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">83</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv; COLOR: #333399; FONT-SIZE: 8pt; FONT-WEIGHT: bold">REVERSE OSMOSIS: Mem Alk Clnr</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">14</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">0.500</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">256.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">3.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv">12 </TD><TD style="FONT-FAMILY: Helv">WHEY</TD><TD style="FONT-FAMILY: Helv">REVERSE OSMOSIS</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv">C</TD><TD style="FONT-FAMILY: Helv">Mem Alk Clnr</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">339</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">83</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv; COLOR: #333399; FONT-SIZE: 8pt; FONT-WEIGHT: bold">REVERSE OSMOSIS: Mem Alk Conditioner</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">15</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">0.571</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">256.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">3.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv">12 </TD><TD style="FONT-FAMILY: Helv">WHEY</TD><TD style="FONT-FAMILY: Helv">REVERSE OSMOSIS</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv">CA</TD><TD style="FONT-FAMILY: Helv">Mem Alk Conditioner</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">340</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">83</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv; COLOR: #333399; FONT-SIZE: 8pt; FONT-WEIGHT: bold">REVERSE OSMOSIS: Mem Conditioner</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">22</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">0.125</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">256.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">3.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv">12 </TD><TD style="FONT-FAMILY: Helv">WHEY</TD><TD style="FONT-FAMILY: Helv">REVERSE OSMOSIS</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv">CB</TD><TD style="FONT-FAMILY: Helv">Mem Conditioner</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">341</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">83</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv; COLOR: #333399; FONT-SIZE: 8pt; FONT-WEIGHT: bold">REVERSE OSMOSIS: Mem Cond Soak</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">17</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">0.500</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">256.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">1.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv; COLOR: #333399; FONT-WEIGHT: bold">4.00</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Helv">12 </TD><TD style="FONT-FAMILY: Helv">WHEY</TD><TD style="FONT-FAMILY: Helv">REVERSE OSMOSIS</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Helv">E</TD><TD style="FONT-FAMILY: Helv">Mem Cond Soak</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C337</TD><TD>=TRIM(VLOOKUP(B337,EQUIPMENT_ITEM_LIST!$B$8:$D$202,3)&": "&VLOOKUP(SURVEY_BREAKDOWN!D337,PRICE_LIST_DATA!$L$12:$S$34,8))</TD></TR><TR><TD>J337</TD><TD>=IF(B337=0,0,VLOOKUP(B337,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,2))</TD></TR><TR><TD>K337</TD><TD>=IF(B337=0,0,VLOOKUP(B337,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,3))</TD></TR><TR><TD>M337</TD><TD>=VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)</TD></TR><TR><TD>C338</TD><TD>=TRIM(VLOOKUP(B338,EQUIPMENT_ITEM_LIST!$B$8:$D$202,3)&": "&VLOOKUP(SURVEY_BREAKDOWN!D338,PRICE_LIST_DATA!$L$12:$S$34,8))</TD></TR><TR><TD>J338</TD><TD>=TRIM(IF(B338=0,0,VLOOKUP(B338,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,2)))</TD></TR><TR><TD>K338</TD><TD>=TRIM(IF(B338=0,0,VLOOKUP(B338,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,3)))</TD></TR><TR><TD>M338</TD><TD>=VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)</TD></TR><TR><TD>C339</TD><TD>=TRIM(VLOOKUP(B339,EQUIPMENT_ITEM_LIST!$B$8:$D$202,3)&": "&VLOOKUP(SURVEY_BREAKDOWN!D339,PRICE_LIST_DATA!$L$12:$S$35,8))</TD></TR><TR><TD>J339</TD><TD>=TRIM(IF(B339=0,0,VLOOKUP(B339,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,2)))</TD></TR><TR><TD>K339</TD><TD>=TRIM(IF(B339=0,0,VLOOKUP(B339,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,3)))</TD></TR><TR><TD>M339</TD><TD>=VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)</TD></TR><TR><TD>C340</TD><TD>=TRIM(VLOOKUP(B340,EQUIPMENT_ITEM_LIST!$B$8:$D$202,3)&": "&VLOOKUP(SURVEY_BREAKDOWN!D340,PRICE_LIST_DATA,8))</TD></TR><TR><TD>J340</TD><TD>=IF(B340=0,0,VLOOKUP(B340,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,2))</TD></TR><TR><TD>K340</TD><TD>=IF(B340=0,0,VLOOKUP(B340,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,3))</TD></TR><TR><TD>M340</TD><TD>=VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)</TD></TR><TR><TD>C341</TD><TD>=TRIM(VLOOKUP(B341,EQUIPMENT_ITEM_LIST!$B$8:$D$202,3)&": "&VLOOKUP(SURVEY_BREAKDOWN!D341,PRICE_LIST_DATA!$L$12:$S$35,8))</TD></TR><TR><TD>J341</TD><TD>=TRIM(IF(B341=0,0,VLOOKUP(B341,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,2)))</TD></TR><TR><TD>K341</TD><TD>=TRIM(IF(B341=0,0,VLOOKUP(B341,EQUIPMENT_ITEM_LIST!$B$8:$E$3004,3)))</TD></TR><TR><TD>M341</TD><TD>=VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Here is the table and formula I am using to fill in my table. What I need to accomplish is to keep each combination of lookup in same same line (paragraph) within the table row. I need to increment the lookup for each row of the table which could have 4 different rows that match; in this case the main lookup is 83&"A" then 83&"B" then 83&"C" then 83&"D". What I need is if there is nothing for each of these, pass through the index and match and result ""; but if there is a match then the formula result.

<!-- ######### Start Created Html Code To Copy ########## -->MAIN_TABLES


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 56px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 180px"><COL style="WIDTH: 138px"><COL style="WIDTH: 138px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>ABL</TD><TD>ABM</TD><TD>ABN</TD><TD>ABO</TD><TD>ABP</TD><TD>ABQ</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Cambria; COLOR: #ff0000; FONT-WEIGHT: bold" colSpan=6>PR.83: REVERSE OSMOSIS</TD></TR><TR style="HEIGHT: 10px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD><TD style="FONT-FAMILY: Times New Roman; FONT-SIZE: 12pt; FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 32px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Step/s</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Chemical/s or
Product/s</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Concentration/s</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Usage/s</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Time/s</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Arial Black; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Temperature/s</TD></TR><TR style="HEIGHT: 247px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

48% Caustic Wash
Surpass 48% Caustic







</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

0.000165 - 0.000275 ozs/gal of
Surpass 48% Caustic per gal







</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">256 gals volume

0% T P, and/or
1.72 PPM











</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD></TR><TR style="HEIGHT: 232px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

Rinse/Post Rinse







</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

N/A







</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A











</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD></TR><TR style="HEIGHT: 247px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

Mem Alk Clnr
Hydriflux NP No. 366

Mem Alk Conditioner
Hydriflux A No. 371

Mem Conditioner
Ultra Surf No. 392



</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">

0.375 - 0.625 ozs/gal of
Hydriflux NP No. 366 per gal

0.42855 - 0.71425 ozs/gal of
Hydriflux A No. 371 per gal

0.09375 - 0.15625 ozs/gal of
Ultra Surf No. 392 per gal



</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">256 gals volume

0.39% T P, and/or
3910 PPM

0.45% T P, and/or
4468.35 PPM

0.1% T P, and/or
977.5 PPM





</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Black; FONT-SIZE: 8pt">N/A</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>ABL2</TD><TD>="PR."&ABR1&": "&VLOOKUP(ABR$1,'C:\Users\ayerse\Documents\A_CABOT\SURVEY\[AA ICR NEW 2012 CABOT.xlsm]EQUIPMENT_ITEM_LIST'!$B$8:$E$103,3)</TD></TR><TR><TD>ABL5</TD><TD>=ROW(5:5)-4</TD></TR><TR><TD>ABM5</TD><TD>=TRIM(LOOKUPS!ABM5)</TD></TR><TR><TD>ABN5</TD><TD>=TRIM(LOOKUPS!ABN5)</TD></TR><TR><TD>ABO5</TD><TD>=TRIM(LOOKUPS!ABO5)</TD></TR><TR><TD>ABP5</TD><TD>=TRIM(LOOKUPS!ABP5)</TD></TR><TR><TD>ABQ5</TD><TD>=TRIM(LOOKUPS!ABQ5)</TD></TR><TR><TD>ABL6</TD><TD>=ROW(6:6)-4</TD></TR><TR><TD>ABM6</TD><TD>=TRIM(LOOKUPS!ABM6)</TD></TR><TR><TD>ABN6</TD><TD>=TRIM(LOOKUPS!ABN6)</TD></TR><TR><TD>ABO6</TD><TD>=TRIM(LOOKUPS!ABO6)</TD></TR><TR><TD>ABP6</TD><TD>=TRIM(LOOKUPS!ABP6)</TD></TR><TR><TD>ABQ6</TD><TD>=TRIM(LOOKUPS!ABQ6)</TD></TR><TR><TD>ABL7</TD><TD>=ROW(7:7)-4</TD></TR><TR><TD>ABM7</TD><TD>=TRIM(LOOKUPS!ABM7)</TD></TR><TR><TD>ABN7</TD><TD>=TRIM(LOOKUPS!ABN7)</TD></TR><TR><TD>ABO7</TD><TD>=TRIM(LOOKUPS!ABO7)</TD></TR><TR><TD>ABP7</TD><TD>=TRIM(LOOKUPS!ABP7)</TD></TR><TR><TD>ABQ7</TD><TD>=TRIM(LOOKUPS!ABQ7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

If there is a better way, I'd love to hear it. If not, help with fitting in the if's so I do not get all the returns would be great.


Excel tables to the web >> Excel Jeanie HTML 4 <!-- ######### End Created Html Code To Copy ########## -->


Excel tables to the web >> Excel Jeanie HTML 4 <!-- ######### End Created Html Code To Copy ########## -->

 

[dougbert]

Hi Ed,

I don't see any examples of INDEX/MATCH formulas to which you refer. Additionally, I don't see an example where nothing would be returned from a lookup, but let me take a stab at your syntax issue when attempting to do a VLOOKUP as an example.

I'm sure others will have their own style for how to return "nothing" when there is nothing to return via a VLOOKUP.

Try this in M337 in the main table. Better still, try it in a cell where you know the result returned will be "nothing".

=IF(ISERROR(VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8)),"N/A",VLOOKUP(SURVEY_BREAKDOWN[[#This Row],[PROD REF]],PRICE_LIST_DATA[#All],8))

This is just a syntax sample of how to do this. You'll see the result clearly as "N/A", if you use it in a "nothing to return" cell. This will eliminate the CHAR(10) or anything else in the lookup cell when there's nothing to return. This works well for me when the lookup values are expected to be TEXT.

Numbers will require a different variation of the formula, but let's start with this example first.

Including your sample is helpful, but it would be more helpful to include examples where there's "nothing" to return.

-dougbert

 

[ed.ayers315]

Dougbert,

Thanks for your quick reply!

Sorry for the error. I am using two same size tables to try and get this done, the one on the original post is the finished table with the trim function and the one added with this reply the long index and match formulas.

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
	ABL	ABM	ABN	ABO	ABP	ABQ
2	PR.83: REVERSE OSMOSIS
3
4	Step/s	Chemical/s orProduct/s	Concentration/s	Usage/s	Time/s	Temperature/s
5	83A	48% Caustic WashSurpass 48% Caustic	0.000165 - 0.000275 ozs/gal ofSurpass 48% Caustic per gal	256 gals volume0% T P, and/or1.72 PPM	N/A	N/A
6	83B	Rinse/Post Rinse	N/A	N/A	N/A	N/A
7	83C	Mem Alk ClnrHydriflux NP No. 366Mem Alk ConditionerHydriflux A No. 371Mem ConditionerUltra Surf No. 392	0.375 - 0.625 ozs/gal ofHydriflux NP No. 366 per gal0.42855 - 0.71425 ozs/gal ofHydriflux A No. 371 per gal0.09375 - 0.15625 ozs/gal ofUltra Surf No. 392 per gal	256 gals volume0.39% T P, and/or3910 PPM0.45% T P, and/or4468.35 PPM0.1% T P, and/or977.5 PPM	N/A	N/A
LOOKUPS

<!-- ######### End Created Html Code To Copy ########## -->

 

[dougbert]

Hi Ed,

Wow, you write longer formulas than I do and that's saying something! However, rather that analyze the formulas themselves, let's talk about all the CHAR(10)'s you've inserted into your formulas. The first thing I'd do is remove some of the CHAR(10)'s from your formulas. Specifically, remove the CHAR(10)&CHAR(10)& after the = sign. Do the same for the beginning of the 'IF TRUE' portion of your formulas. If the lookup table contains 'line feeds', remove them if they aren't necessary, as it makes the criteria for your lookup longer and more comple.

After those are gone, write your formulas so that a CHAR(10) is ONLY placed into your results IF the formula's 'logical test' evaluates as TRUE. Even then, ONLY use a CHAR(10) to create a 'line feed' if absolutely necessary. Additionally, don't use a CHAR(10) to insert 'white space' before or after your 'IF TRUE' result.

I noticed that your results within columns have similar lengths. Use Excel's formatting features instead of CHAR(10)'s in order to get a nice looking display of data results. On some of your columns I think you could use Word Wrap and then change the column width until your line breaks where you want it to break.

On the Home tab of the ribbon, use the Alignment section and CENTER your result, if that's what you desire. Under the Cells section, choose the FORMAT pull-down and Auto-fit your row height. You can do all rows at once by highlighting all the rows first before you AutoFit them. That way you won't have all that extra 'white space', unless of course, that's what you want. For the column width, place your cursor over the line between the column letters, click and drag until your column width is correct to display the width of the widest line of text in that column. Or, just double-click on the line and it will automatically re-size it for you.

Using Excel's formatting features may allow you to get the look you want without having to place unnecessary CHAR(10)'s within your formulas. Experiment! I think you'll see what I mean.

Good Luck!
-dougbert

 

[ed.ayers315]

Thanks Dougbert,

Great points.

Do you have a suggestion for a macro to find the printable charater in a cell and replace any char(10) after that?