If else statement with Vlookup.

%3CLINGO-SUB%20id%3D%22lingo-sub-2700652%22%20slang%3D%22en-US%22%3EIf%20else%20statement%20with%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rossshah_0-1630364154001.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F306812i3FDE607D3BFE6AB3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rossshah_0-1630364154001.png%22%20alt%3D%22rossshah_0-1630364154001.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHello%2C%26nbsp%3B%20I%20would%20like%20to%20know%20how%20to%20show%20age%20on%20cell%20G2%2C%20only%20if%20john%20belongs%20to%20group%20b.%26nbsp%3B%20In%20above%20formula%2C%20Vlookup%20returned%20john's%20age%2026%2C%20but%20john%20is%20not%20in%20group%20b.%26nbsp%3B%20%26nbsp%3BIt%20should%20be%20blank.%26nbsp%3B%20Could%20anyone%20tell%20me%20the%20formula%20please.%26nbsp%3B%20%26nbsp%3BBasically%20it%20is%20if%20john%20is%20in%20group%20b%2C%20then%20vlookup%20to%20give%20john's%20age.%26nbsp%3B%20%26nbsp%3BThank%20you.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2700652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2701262%22%20slang%3D%22en-US%22%3ERe%3A%20If%20else%20statement%20with%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2701262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140763%22%20target%3D%22_blank%22%3E%40rossshah%3C%2FA%3E%26nbsp%3BIn%20its%20crudest%20form%20you%20could%20use%20in%20G2%20(and%20fill%20down%20for%20the%20others%2C%20if%20that%20is%20your%20intention)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT(%24C%242%3A%24C%245*(F2%3D%24A%242%3A%24A%245)*(%22b%22%3D%24B%242%3A%24B%245))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3Bbut%20better%20to%20use%20named%20ranges%20to%20avoid%20the%20(absolute)%20cell%20references%20and%20hard%20coded%20criteria%20like%20%22b%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2702221%22%20slang%3D%22en-US%22%3ERe%3A%20If%20else%20statement%20with%20Vlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1140763%22%20target%3D%22_blank%22%3E%40rossshah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%40IFERROR(%20FILTER(age%2C%20(group%3D%22b%22)*(name%3D%22john%22))%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

rossshah_0-1630364154001.png

Hello,  I would like to know how to show age on cell G2, only if john belongs to group b.  In above formula, Vlookup returned john's age 26, but john is not in group b.   It should be blank.  Could anyone tell me the formula please.   Basically it is if john is in group b, then vlookup to give john's age.   Thank you.  

2 Replies

@rossshah In its crudest form you could use in G2 (and fill down for the others, if that is your intention):

=SUMPRODUCT($C$2:$C$5*(F2=$A$2:$A$5)*("b"=$B$2:$B$5))

 but better to use named ranges to avoid the (absolute) cell references and hard coded criteria like "b". 

@rossshah 

As variant like

=@IFERROR( FILTER(age, (group="b")*(name="john")), "")