This site explains how to perform vlookup on multiple criteria using an array
I modified the forumla slightly to provide a "0" response if one of the lookup values is blank, thus preventing a N/A# error
{=IF(R5="",0,(INDEX('Rate Table'!$F$5:$F$63,MATCH(A5&D5&R5,'Rate Table'!$B$5:$B$63&'Rate Table'!$E$5:$E$63&'Rate Table'!$A$5:$A$63,0)))}
The absolute key here is that you cannot just hit ENTER, you must hit ENTER+SHIFT+CTRL in order for excel to recognize this as an array formula. You do not enter the "squiggly" brakets, excel will do that automatically if the formula is entered correctly.
No comments:
Post a Comment