Any way to lookup based on multiple criteria in Google Spreadsheets?

Question

I am trying to use a lookup based on multiple criteria in Google Docs

I am using an INDEX/MATCH formula that works for me in Excel

=index('EXA Master Matrix'!A3:Z199,MATCH(B27&D27&E27&F27,'EXA Master Matrix'!$A$4:$A&'EXA Master Matrix'!$B$4:$B&'EXA Master Matrix'!$D$4:$D&'EXA Master Matrix'!$R$4:$R,0),18)

It's not returning the right one though Any idea how to replicate this for Google Docs?

I am basically looking for B27, D27, E27 and F27 to match in the EXA Master Matrix spreadsheet, and then for it to display that row the 18th value (hence the ,18))

Any help appreciated

Answer 1

This works well for me:

=INDEX ( 'Staffing-Table'!$E$2:$E$176 , ( MATCH ( $A9 & $B9 & "Radiologist" , 'Staffing-Table'!$A$2:$A$176 & 'Staffing-Table'!$B$2:$B$176 & 'Staffing-Table'!$D$2:$D$176, 0 )))

I think that you should give this a read: http://www.randomwok.com/excel/how-to-use-index-match/

Basically your INDEX first variable should say what column you want returned, so in this case it would be the 18th column, not the entire table.

Answer 2

A somewhat simplistic way to do it is concatenate the multiple criteria in a separate column and then do a look up on that.