Lookup formula

[aryanaveen]

Hi Guys,

can someone please help me in building a right lookup up formula

	A	B	C	D
1​	Id	Name	Birth date	Age
2​	55​	ABC	14-Aug-20​	25​
3​	55​	ABC	14-Jul-20​	34​
4​	55​	ABC	14-Jun-20​	33​
5​	22​	FF	24-May-20​	22​
6​	33​	SS	10-Sep-20​	15​
7​	33​	SS	5-Aug-20​	20​
8​
9​
10​	What I am looking for is
11​
12​	Id	Name	Birth date	Age
13​	55​	ABC	14-Jun-20​		Need formula which will give me age based on ID and birth date
14​	33​	SS	5-Aug-20​

 

[jorismoerings]

Hi,

try any of the 2 options:

Book1
	A	B	C	D	E
1	Id	Name	Birth date	Age
2	55	ABC	14-8-2020	25
3	55	ABC	14-7-2020	34
4	55	ABC	14-6-2020	33
5	22	FF	24-5-2020	22
6	33	SS	10-9-2020	15
7	33	SS	5-8-2020	20
8
9
10	What I am looking for is
11
12	Id	Name	Birth date	Age
13	55	ABC	14-6-2020	33	33
14	33	SS	5-8-2020	20	20
Sheet5

Cell Formulas
Range	Formula
D13:D14	D13	=INDEX($D$2:$D$7,MATCH(1,($A$2:$A$7=A13)*($C$2:$C$7=C13),0))
E13:E14	E13	=SUMPRODUCT(($A$2:$A$7=A13)*($C$2:$C$7=C13)*($D$2:$D$7))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[aryanaveen]

This works great, Thank you Joris

 

[Aladin Akyurek]

The SUMPRODUCT option should be avoided though for reasons of generality and correctness.

 

[Peter_SSs]

please help me in building a right lookup up formula

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365, this also becomes available to you. (Assumed same ID and birth date cannot occur more than once, otherwise a modification would be required)

20 08 14.xlsm
	A	B	C	D
1	Id	Name	Birth date	Age
2	55	ABC	14/8/2020	25
3	55	ABC	14/7/2020	34
4	55	ABC	14/6/2020	33
5	22	FF	24/5/2020	22
6	33	SS	10/9/2020	15
7	33	SS	5/8/2020	20
8
9
10
11
12	Id	Name	Birth date	Age
13	55	ABC	14/6/2020	33
14	33	SS	5/8/2020	20
Age

Cell Formulas
Range	Formula
D13:D14	D13	=FILTER(D$2:D$7,(A$2:A$7=A13)*(C$2:C$7=C13),"")

Otherwise, if you are sure each ID/birth date combination is unique then you could also consider.

20 08 14.xlsm
	A	B	C	D
1	Id	Name	Birth date	Age
2	55	ABC	14/8/2020	25
3	55	ABC	14/7/2020	34
4	55	ABC	14/6/2020	33
5	22	FF	24/5/2020	22
6	33	SS	10/9/2020	15
7	33	SS	5/8/2020	20
8
9
10
11
12	Id	Name	Birth date	Age
13	55	ABC	14/6/2020	33
14	33	SS	5/8/2020	20
Age

Cell Formulas
Range	Formula
D13:D14	D13	=SUMIFS(D$2:D$7,A$2:A$7,A13,C$2:C$7,C13)