Matching 3 criteria using index and match formula

Hi,

I have a sample excel sheet attached, basically I have 3 criteria and want to return the cost from the data table I have tried to use the formula below, works fine when I'm search just 1 cell i.e. B2=A3 but when searching a range B2:G2=A3 it comes back with N/A:

=INDEX(Sheet1!B4:G6,MATCH(1,IF(Sheet1!B2:G2=A3,IF(Sheet1!B3:G3=A4,IF(Sheet1!A4:A6=A6,1))),0))

A3 - Location
A4 - Total Cost
A6 - Manager


I am trying to return the Manager cost from a data table on Sheet 1 B4:G6. If you could help I would be grateful.

Here, try this:

Formula:

Please Login or Register  to view this content.

Important: On sheet1 you have extra spaces after London. Remove those spaces (see mine example) to have exact match.

Hi,

Try below formula in B6 and confirm with Ctrl+Shift+Enter.

=INDEX(Sheet1!$B$4:$G$6,MATCH(Sheet2!A6,Sheet1!$A$4:$A$6,0),MATCH(Sheet2!$A$3&Sheet2!$A$4,Sheet1!$B$2:$G$2&Sheet1!$B$3:$G$3,0))

Also note LONDON on your table has an extra trailing space. Delete it.