Link to home
Start Free TrialLog in
Avatar of Josh House
Josh HouseFlag for United States of America

asked on

SKip past fields with no data

I am trying to build a formula that reference cells in a table. My goal is to only return values that have data in the linear feet and weight in Table1. So if a cell in the table is blank don't return it.  Any ideas on how to fix?

=IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),"")

Open in new window

Carrier-Volume-Rate-Estimate.xlsm
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Just edit above formula with this one:
=IF(B4="","",IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),""))

Open in new window

Please find attached...
Carrier-Volume-Rate-Estimate_v1.xlsm
You can also have below formula, either B4 or C4 is blank, it will result blank:
=IF(OR(B4="",C4=""),"",IFERROR(INDEX(Table1[SCAC],SMALL(IF((Table1[Linear Feet]>=$B$4)*(Table1[Weight]>=$CS$4),ROW(Table1[SCAC])-MIN(ROW(Table1[SCAC]))+1),ROWS(E$3:E3))),""))

Open in new window

Avatar of Josh House

ASKER

Will this formula if linear feet or the weight is greater than or equal to the reference cell return the value
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you so very much, you've been a great help.
You're Welcome Josh! Glad I was able to help again.