Index Match with multiple Match criteria

[stlpony]

Hello, long-time fan of the forum ...

Read a few threads on the topic, but still having issues. On my summary tab I have the name of the sales person (A1), and all their clients (A6:A26).

On the backing data tab, I have company names (Column B), and sales person names (column D) ... I want the value that is in Column E ... This is based on the criteria of A1 and A8 on the summary tab for one example.

How do I link up the multiple matches?

Thanks!

 

[SpillerBD]

Array formula entered with Control+Shift+Enter (CSE)
Type or copy the Blue and the CSE will add the curly brackets for the Array formula.

{[COLOR=#0000ff]=INDEX(BackData!$E$1:$E$40,MATCH(Sheet1!$A$22&Sheet1!A8,BackData!$D$1:$D$40&BackData!$B$1:$B$40,0))[/COLOR]}

You can this on Mike Girvin's YouTube channel ExcelIsFun.

 

[stlpony]

Still getting a "ref" error .... Even changed my sheet names to be identical ... Any idea?

=INDEX(BackData!A1:G2155,MATCH(Sheet1!A2&Sheet1!A19,BackData!D1:D2155&BackData!B1:B2155,0))

<tbody>
</tbody>

Also, while I need A2 on Sheet1 to be fixed, I need the other match critera to move from A6:A26

Thanks for the help!

 

[stlpony]

Got it ... No column number was listed in formula above, thanks for help SpillerBD!

=INDEX(BackData!A1:G2155,MATCH(Sheet1!A2&Sheet1!A19,BackData!D1:D2155&BackData!B1:B2155,0),5)

<tbody>
</tbody>

 

[SpillerBD]

You only need the column number when your lookup range is more than 1 column wide.
To me there is no reason to use more than one column....
I would use

INDEX(BackData!G1:G2155,MATCH(Sheet1!A2&Sheet1!A19,BackData!D1:D2155&BackData!B1:B2155,0))

When you get into 10k or more ranges, you definitely want to limit that range... Or use a different technique all together.