Finding matching data

[garyt153]

Hello, I have a workbook with 2 sheets.

Sheet 1 has 4 columns as below:

DATE	PLACE	NAME	ANSWER
12-Jan	SYD	JOHN	?????

sheet 2 has the same 4 columns but with data in Col D.

DATE	PLACE	NAME	DATA
1-Jan	SYD	HARRY	9.0
2-Jan	MELB	FRED	2.5
3-Jan	PER	JOHN	2.0
4-Jan	MEL	SAM	3.5
5-Jan	SYD	PETER	3.5
6-Jan	BRIS	HARRY	4.0
7-Jan	ADE	SAM	5.0
8-Jan	MEL	JACK	5.0
9-Jan	SYD	JOHN	6.5
10-Jan	DAR	HARRY	16.5
11-Jan	PER	FRED
12-Jan	SYD	JOHN	7.5
13-Jan	BRIS	SAM	10.0
14-Jan	ADE	PETER	9.0
15-Jan	MEL	HARRY	12.5
16-Jan	SYD	SAM	8.0
17-Jan	DAR	JACK	10.5
18-Jan	PER	JOHN	10.8

I am trying to set up a formula to match the data in sheet 1 and find the matching data in sheet 2 - then deliver the missing data in sheet 1 Col D.

I hope I have explained it properly. In sheet 1, if A1=12 Jan, B1=SYD, C1=John is there a formula that would search sheet 2 for the correct matching data row and insert the answer (7.5) into sheet 1 D1?

Many thanks

 

[SunnyAlv]

like this ?
Sheet 1 is criteria
Sheet 2 is Database

=INDEX(Sheet2!D1:D19,MATCH(1,(Sheet2!A1:A19=Sheet1!A2)*(Sheet2!B1:B19=Sheet1!B2)*(Sheet2!C1:C19=Sheet1!C2),0))

 

[Sanjeev1976]

You can also try the below formula :

=XLOOKUP(CONCAT(A2:C2),Sheet2!A:A&Sheet2!B:B&Sheet2!C:C,Sheet2!D:D,"")

 

[kvsrinivasamurthy]

In D2 of sheet1, spill out formula

=LET(a,A2:A4&B2:B4&C2:C4,da,Sheet2!A2:A19&Sheet2!B2:B19&Sheet2!C2:C19,dd,Sheet2!D2:D19,XLOOKUP(a,da,dd,"",0))

Shert1

DATE	PLACE	NAME	ANSWER
12-Jan	SYD	JOHN	7.5
05-Jan	SYD	PETER	3.5
08-Jan	MEL	JACK	5

Sheet2

DATE	PLACE	NAME	DATA
01-Jan	SYD	HARRY	9
02-Jan	MELB	FRED	2.5
03-Jan	PER	JOHN	2
04-Jan	MEL	SAM	3.5
05-Jan	SYD	PETER	3.5
06-Jan	BRIS	HARRY	4
07-Jan	ADE	SAM	5
08-Jan	MEL	JACK	5
09-Jan	SYD	JOHN	6.5
10-Jan	DAR	HARRY	16.5
11-Jan	PER	FRED
12-Jan	SYD	JOHN	7.5
13-Jan	BRIS	SAM	10
14-Jan	ADE	PETER	9
15-Jan	MEL	HARRY	12.5
16-Jan	SYD	SAM	8
17-Jan	DAR	JACK	10.5
18-Jan	PER	JOHN	10.8

 

[Fluff]

Another option

=FILTER(Sheet2!D2:D100,(Sheet2!A2:A100=A2)*(Sheet2!B2:B100=B2)*(Sheet2!C2:C100=C2))