Excel Return Corresponding Row from Non-Blank Cell

0
votes

I have a table in Excel that calculates the price of software.

Product   Qty    Cost
A         1      $100
B
C
D         2      $250

I am trying to create a sheet that just returns the Product name from the corresponding non-blank Cost column. So it would look like

Product   Cost
A         $100
D         $250

I am able to populate the Cost column since that is a basic INDEX and MATCH but the difficult part is having it populate the Product name only from the non-blank costs. How would I do this?

I was able to find the value of the first non-blank cell but I could not have it return the subsequent non-blank cells.

1
excelexcel-formula
Do you want to include the product if the qty/cost is zero? – user4039065
@Jeeped there will never be a 0 value. So I just want to skip blanks – Bijan

1 Answers

2
votes

To get the products with non-blank qty/cost,

=INDEX(A:A, AGGREGATE(15, 6, ROW(A$2:INDEX(A:A, MATCH(1E+99, B:B)))/SIGN(LEN(B$2:INDEX(B:B, MATCH(1E+99, B:B)))), ROW(1:1)))
'alternate to discard non-blank, zero value cells
=INDEX(A:A, AGGREGATE(15, 6, ROW(A$2:INDEX(A:A, MATCH(1E+99, B:B)))/(B$2:INDEX(B:B, MATCH(1E+99, B:B))<>0), ROW(1:1)))

An additional VLOOKUP or INDEX/MATCH formula will return the cost associated with each product.