SOLVED

Lookup for values in a column different than zero/empty

Copper Contributor

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. 

 

IDPrice (% Quoted)
Product A0
Product A93,336
Product A0
Product B0
Product B0
Product B0
Product B157,706
Product B0
Product C0
Product C175,21
Product C0
Product C0
Product C0

 

Thank you and best regards

 

 

6 Replies

@Inesmateus 

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.

 

S0598.png

@Hans Vogelaar 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.
best response confirmed by Inesmateus (Copper Contributor)
Solution

@Inesmateus 

As variant

image.png

with

=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))

@Inesmateus 

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.

Works perfectly. Thank you.
1 best response

Accepted Solutions
best response confirmed by Inesmateus (Copper Contributor)
Solution

@Inesmateus 

As variant

image.png

with

=INDEX(Table1[Price (% Quoted)], MATCH(1, INDEX( (Table1[ID]=E3)*(Table1[Price (% Quoted)]<>0)*(Table1[Price (% Quoted)]<>""),0),0))

View solution in original post