We help IT Professionals succeed at work.

SKip past fields with no data

76 Views
Last Modified: 2017-03-15
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
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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

Josh HouseDigital Sales Coordinator

Author

Commented:
Will this formula if linear feet or the weight is greater than or equal to the reference cell return the value
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Josh HouseDigital Sales Coordinator

Author

Commented:
thank you so very much, you've been a great help.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Josh! Glad I was able to help again.