help troubleshoot an INDEX/MATCH formula

hello, I am trying to use the formula below to return a value from another sheet in the attached workbook. It is giving me a #NA error. I know the correct data is there so I suspect I have not written the formula correctly as this is my fist go round with INDEX/MATCH and it is proving to be quite elusive. In the attached workbook, the yellow columns are where the formula's will be located. The blue columns are the reference columns. The orange cells are my reference cells and the green cells are where my formula is located and the intended result.

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Clayton

plug this in Overall review N6

=VLOOKUP($A6,Data!$I$2:$L$520,4,0)

I think perhaps you want something more along the lines of:

=LOOKUP(2,1/((Data!$I$2:$I$520=$A6)*(Data!$G$2:$G$520=N$3)),Data!$L$2:$L$520)

Your Match is based on two values rather than one so a standard MATCH won't suffice unless you concatenate the two fields in a new column
(which would be my advice incidentally)

The LOOKUP approach works by finding the last instance where both criteria are found in the same row - and returns the associated value from column L
(you will get #N/A should no match exist)

LOOKUP differs from VLOOKUP and INDEX / MATCH (0) in that it finds the last rather than the first match - here there would appear to be only one match (if any) so it's not [seemingly] an issue.

link to some guff on LOOKUP used in this context: http://www.excelforum.com/2252293-post5.html (and some sub links within)

Similarly, and a little more familiar looking:

=INDEX(Data!$L$2:$L$520,(MATCH(1,INDEX((Data!$I$2:$I$520=$A6)*(Data!$G$2:$G$520=N$3),0),0)))

would find you the first match.. which if matches are unique, would end up the same match as DO's Lookup(2,1/())) formula....

Sorry, meant to start a new thread