CODEWITHSUNDEEP
excelexcel-formula
Saguro
Saguro

Reputation: 131

VLOOKUP with criterion of max

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

Upvotes: 0

Views: 172

Answers (3)

Harun24hr
Harun24hr

Reputation: 37050

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.

enter image description here

Upvotes: 0

sous2817
sous2817

Reputation: 3960

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

enter image description here

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

Upvotes: 1

Dominique
Dominique

Reputation: 17565

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)

Upvotes: 2

Related Questions