Aug 30 2021 04:01 PM
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.
Aug 30 2021 10:33 PM
@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".
Aug 31 2021 03:32 AM