Jul 19 2021 06:58 AM
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
Jul 19 2021 07:04 AM
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.
Jul 19 2021 07:11 AM
Jul 19 2021 07:12 AM
SolutionAs variant
with
=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))
Jul 19 2021 07:15 AM
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.
Jul 19 2021 07:25 AM
Jul 19 2021 10:06 AM
@Inesmateus , you are welcome
Jul 19 2021 07:12 AM
SolutionAs variant
with
=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))