Any way to search by multiple criteria in Google Spreadsheets?

Question

I am trying to use multiple search criteria in Google Docs

I use the 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 does not return the correct one, although Any idea to replicate this for Google Docs?

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

Any help appreciated

Answer 1

A somewhat simplified way to do this is to combine several criteria in a separate column, and then look at it.

Answer 2

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 you should read this: http://www.randomwok.com/excel/how-to-use-index-match/

Basically, your first INDEX variable should indicate which column you want to return, so in this case it will be the 18th column, not the whole table.

Answer 3

This syntax can be used if you have several criteria.

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