Forum Discussion
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 different flows, but there are several entries for the same ID and only one of the row have the price. I need to have a similar VLOOKUP formula to extract only the price.
| ID | Price (% Quoted) |
| Product A | 0 |
| Product A | 93,336 |
| Product A | 0 |
| Product B | 0 |
| Product B | 0 |
| Product B | 0 |
| Product B | 157,706 |
| Product B | 0 |
| Product C | 0 |
| Product C | 175,21 |
| Product C | 0 |
| Product C | 0 |
| Product C | 0 |
Thank you and best regards
As variant
with
=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))
6 Replies
As variant
with
=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))- Works perfectly. Thank you.
Inesmateus , you are welcome
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.
