look up a value multiple criteria

[Memar]

What is the best excel function or functions to look up a value from a different worksheet (workbook B) with three criteria without suffering from slow Excel spreadsheet and be able to close the workbook B when I don’t have to work on it.

Excel Function Tried	Advantage	Disadvantage
Index and Match	I don’t have to open workbook B to retrieve the value in workbook A	It makes the spreadsheet slow and take about a minute to open the workbooks
SUMIFS	I have to open workbook B all the time to see the values in workbook A. When workbook B closed it gives me #VALUE!	The speed is normal	=SUMIFS(workbookB!L2:L30000,workbookB!A2:A30000, A4,WorkbookB!B2:B30000,C2,Workbook!D2:D30000,D2)
SUMPRODUCT	It gives me 0 value		=SUMPRODUCT(workbookB!L2:L30000,--(WorkbookB!A2:A30000=A4),--(WorkbookB!B2:B30000=C2),--(Workbook!D2:D30000=D2))

<tbody>
</tbody>

Thank you for your time and help!

 

[Domenic]

For efficiency, use helper columns, along with INDEX/MATCH, as per the 2nd example on the following page...

http://xl-central.com/lookup-multiple-criteria.html

Hope this helps!