Formula help VLOOKUP?XLOOKUP?

%3CLINGO-SUB%20id%3D%22lingo-sub-1615249%22%20slang%3D%22en-US%22%3EFormula%20help%20VLOOKUP%3FXLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615249%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Could%20you%20please%20help%20me%20on%20this%3F%3C%2FP%3E%3CP%3EThere%20are%20names%20of%20students%20in%20first%20column%20and%20their%20age%20in%20second%20column.%20There%20are%20many%20same%20name%2C%20same%20age%20students%2C%20only%20few%20same%20name%20students%20have%20different%20age.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20know%20which%20same%20name%20students%20have%20same%20age%20and%20which%20does%20not.%20If%20same%20age%2C%20it%20should%20display%201%20in%20the%20next%20column%20and%200%20if%20different.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1615249%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615295%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3FXLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772810%22%20target%3D%22_blank%22%3E%40sagarpdl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20a%20column%20combining%20Name%20and%20Age%20then%20used%20a%20count%20if%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20solves%20the%20problem%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615297%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3FXLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615297%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772810%22%20target%3D%22_blank%22%3E%40sagarpdl%3C%2FA%3E%2C%20as%20entered%20in%20the%20attached%20file%2C%20the%20formula%20in%20C2%20to%20determine%20whether%20or%20not%20a%20student%20has%20the%20same%20name%20and%20age%20as%20another%20is%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--(SUMPRODUCT(--(A%3AA%26amp%3BB%3AB%3DA2%26amp%3BB2))%26gt%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615330%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3FXLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772810%22%20target%3D%22_blank%22%3E%40sagarpdl%3C%2FA%3E%2C%20as%20entered%20in%20C2%20in%20the%20attached%20file%2C%20the%20formula%20to%20determine%20whether%20or%20not%20a%20student%20has%20the%20same%20name%20and%20age%20as%20another%20is%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--(SUMPRODUCT(--(A%3AA%26amp%3BB%3AB%3DA2%26amp%3BB2))%26gt%3B1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615611%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%20VLOOKUP%3FXLOOKUP%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772810%22%20target%3D%22_blank%22%3E%40sagarpdl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20vote%20would%20go%20to%20the%20IFS%20family%20of%20functions%20which%20are%20several%20times%20faster%20than%20normal%20array%20formulas.%26nbsp%3B%20With%20modern%20dynamic%20arrays%2C%20a%20single%20spilt%20range%20would%20be%20given%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SIGN(%20COUNTIFS(Name%2C%20Name%2C%20Age%2C%20Age)%20%26gt%3B%201%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eor%2C%20within%20an%20Excel%20Table%2C%20the%20column%20formula%20should%20return%20a%20single%20cell%20at%20a%20time%2C%20so%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SIGN(%20COUNTIFS(%5BName%5D%2C%20%5B%40Name%5D%2C%20%5BAge%5D%2C%20%5B%40Age%5D)%20%26gt%3B%201%20)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

3 Replies

@sagarpdl 

I made a column combining Name and Age then used a count if formula

 

Hope it solves the problem

@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&B:B=A2&B2))>1)

@sagarpdl 

My vote would go to the IFS family of functions which are several times faster than normal array formulas.  With modern dynamic arrays, a single spilt range would be given by

= SIGN( COUNTIFS(Name, Name, Age, Age) > 1 )

or, within an Excel Table, the column formula should return a single cell at a time, so

= SIGN( COUNTIFS([Name], [@Name], [Age], [@Age]) > 1 )