Lookup 3 column array.

[DHayes]

Good day,
I am struggling with my formula. hopefully I can get some help. I need to compare 3 columns in sheet 1 with sheet 2 and give me the result in column D. I am currently using the following formula. I am not sure what I am doing incorrectly.

=INDEX(Sheet2!D:D;MATCH(1;(Sheet2!A:A=A1)*(Sheet2!B:B=B1)*(Sheet2!C:C=C1);0))

Sheet1
town	erf	prt
HUMANSDORP	886	0
HUMANSDORP	889	0
THORNHILL	123	1
HUMANSDORP	455	0
HUMANSDORP	229	0
Sheet 2
town	erf	prt	Value
HUMANSDORP	886	0	100000
JEFFREYS BAY	886	0	120000
CAPE ST FRANCIS	886	0	1200000
KRUISFONTEIN	886	0	540000
PARADYSSTRAND	886	0	230000
ASTON BAY	120	0	450000
GAMTOOS MOUTH	120	0	10000
HANKEY	120	0	120000
HUMANSDORP	120	0	560000
JEFFREYS BAY	120	0	1500000
CAPE ST FRANCIS	120	0	3500000
Thornhill	123	1	23000
OESTERBAAI	120	0	150000

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[DHayes]

No Problem came right.

 

[pjmorris]

Hi, try this: =SUMPRODUCT(--(Sheet2!$A$2:$A$40=Sheet1!A2);--(Sheet2!$B$2:$B$40=Sheet1!B2);--(Sheet2!$C$2:$C$40=Sheet1!C2);Sheet2!$D$2:$D$40)

Note unless you really need to refer to entire columns I would limit the range to whatever is necessary in order to speed up the process.

Hope this helps.

Regard

 

[DHayes]

Thank you, I will try yours. I did limit to the columns.

 

[pjmorris]

Hi, do let me know if it works or even if it doesnt!

 

[DHayes]

I did try it and it did not seem to work. I did come right with my one i forgot to CTL+SHIFT+ENTER.

 

[pjmorris]

hmm, don't know why, though you've not shown the output you expect from the data given above so I may have misinterpreted the question.

Anyway, delighted you've got a solution even if it does use an array formula (I just have an aversion to them!).

Regards

 

[SpillerBD]

Should be

{=INDEX(Sheet2!$D:$D,MATCH(A1&B1&C1,Sheet2!A:A&Sheet2!B:B&Sheet2!C:C,0))}

This is an formula entered as an Array formula, meaning you DO NOT enter the brackets { }
You enter the formula and the use CTRL + SHIFT + ENTER

And yes, while the ranges indicate the entire column, it would be better to limit those ranges to speed up what can be a cumbersome formula.