Forum Discussion
Solved
Lookup for values in a column different than zero/empty
Dear all, There is a way to lookup for values in a specific column but ignore empty cells or cells with zero? For instance, I have a flow table, by product in which each line represent a differ...
As variant
with
=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))
You could create a pivot table based on the flow table.
Add ID to the Rows area and Price (% Quoted) to the Values area.
Change the summary function for the value field to Max.
HansVogelaar Thank you for your reply. I need to build a new organized table based on this information so I would like to have a way to push directly from first table, using formula if possible.
Let's say your data are in A1:B100, with headers in row 1.
Enter the products in D2, D3 etc.
In E2, enter the formula
=MAXIFS($B$2:$B$100,$A$2:$A$100,D2)
or if you have Excel 2016 or earlier, the following array formula confirmed with Ctrl+Shift+Enter:
=MAX(IF($A$2:$A$100=D2,$B$2:$B$100))
Then fill down.