Formula help VLOOKUP?XLOOKUP?

Question

Hi, Could you please help me on this?There are names of students in first column and their age in second column. There are many same name, same age students, only few same name students have different age.I would like to know which same name students have same age and which does not. If same age, it should display 1 in the next column and 0 if different.&nbsp;

ramiz_assaf · Answer

sagarpdl&nbsp;I made a column combining Name and Age then used a count if formula&nbsp;Hope it solves the problem

twifoo · Answer

sagarpdl, as entered in C2 in the attached file, the formula to determine whether or not a student has the same name and age as another is:=--(SUMPRODUCT(--(A:A&amp;B:B=A2&amp;B2))&gt;1)

peterbartholomew1 · Answer

sagarpdl&nbsp;My vote would go to the IFS family of functions which are several times faster than normal array formulas.&nbsp; With modern dynamic arrays, a single spilt range would be given by= SIGN( COUNTIFS(Name, Name, Age, Age) &gt; 1 )or, within an Excel Table, the column formula should return a single cell at a time, so= SIGN( COUNTIFS([Name], [@Name], [Age], [@Age]) &gt; 1 )

Forum Discussion

Formula help VLOOKUP?XLOOKUP?

3 Replies