VLOOKUP with criterion of max-CodePudding

lets say I have a Table1 as follow:

ID    |  Value  
________________  
 1    |    0    
 2    |    0    
 1    |    1    
 3    |    1    
 1    |    0    
 2    |    0   
 1    |    0    
 2    |    0  
 3    |    0    
 4    |    1  
 1    |    0    
 5    |    0

and I have a second table that contains unique IDs from Table1. In Table1 ID may repeat, but each ID can have at most one 1 in Value column, the rest is 0. How can I write VLOOKUP like formula that will tell me if given ID has 1 in any occurence?

I would like to get smth like

ID    |  Value  
________________  
 1    |    1    
 2    |    0    
 3    |    1    
 4    |    1    
 5    |    0

with SQL I would write smth as SELECT ID, max(Value) from Table1 group by ID, or even instead of max would use sum. Also to mention: Table1 will be in separate file from my output table and the Value will be just one of many columns, therefore I cannot use Pivot Tables

CodePudding user response：

I think the solution is easier than you might think:

=SUMIFS(B$2:B$13,A$2:A$13,1)

What are you doing? You are summing everything? I just want to know where the 1 is, no need to sum it?
Well: you seem to have two possible values: either all 0's, either all 0's and just one 1: if you search for that 1, or if you take the sum, the result is the same :-)

Ok, that's a neat trick, but what if I decide there might be more than one 1?
Well: just translate a number, larger than 1, to 1, which you can do with this formula:

=IF(E2,1,0)

CodePudding user response：

There are several ways to go about it, and I'm assuming that your values are more complicated than your example, so here is one way:

=MAX(IF(A$2:A$13=E3,B$2:B$13))

Where A2:A13 is your IDs, B2:B13 is the value, and E3 is the start of your reference table. This is an array formula and needs to be confirmed with CTRL SHIFT ENTER

If it's as simple as 1 or 0, you should use the answer that @dominique gave.

CodePudding user response：

Give a try on the following formula-

=HSTACK(UNIQUE(A2:A13),MAXIFS(B2:B13,A2:A13,UNIQUE(A2:A13)))

This will work like SQL. It will also work if you have more values than one.