Index/Match Across Workbooks with Multiple Criteria

[OandGmodeling]

Hello,

I am trying to pull certain data from a raw data file that is updated weekly from another source and place it in a more presentable model. I am having to use multiple criteria (Columns A and B below) to try and get the appropriate lookup value from Column C through F but am not having much luck. I am also trying to make the formula flexible since the data range will likely change on a weekly basis. My lookup values are from the model and are presented in the exact same text as in the raw file. Thanks!

Here is the formula I am using that is returning a #VALUE error when not used as an array, which I would prefer not to use if possible:

=INDEX([Rigs_04012019.xlsx]Rigs!$C:$C,MATCH($B$103&B$104,[Rigs_04012019.xlsx]Rigs!$A:$A&[Rigs_04012019.xlsx]Rigs!$B:$B,0))

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Play[/TD]
[TD]Operator (reported)[/TD]
[TD]Drilling[/TD]
[TD]Formation[/TD]
[TD]County[/TD]
[TD]Depth[/TD]
[/TR]
[TR]
[TD][Delaware][/TD]
[TD]Standard Oil[/TD]
[TD]Y[/TD]
[TD]Reeves[/TD]
[TD]Bone Spring[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD][Midland][/TD]
[TD]Standard Oil[/TD]
[TD]N[/TD]
[TD]Martin[/TD]
[TD]Wolf Camp[/TD]
[TD]15000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

"more presentable model." Thats actually what you should be using, Data Model.
Using PowerQuery/Get and Transform.

 

[DanteAmor]

Try this

=INDICE([Rigs_04012019.xlsx]Rigs!$C$2:$C$20,
SUMAPRODUCTO(([Rigs_04012019.xlsx]Rigs!$A$2:$A$20=B103)*
([Rigs_04012019.xlsx]Rigs!$B$2:$B$20=B104)*
(FILA([Rigs_04012019.xlsx]Rigs!$A$2:$A$20)))-1)

 

[MARK858]

Or in English

=INDEX([Rigs_04012019.xlsx]Rigs!$C$2:$C$20,
SUMPRODUCT(([Rigs_04012019.xlsx]Rigs!$A$2:$A$20=B103)*
([Rigs_04012019.xlsx]Rigs!$B$2:$B$20=B104)*
(ROW([Rigs_04012019.xlsx]Rigs!$A$2:$A$20)))-1)

 

[DanteAmor]

Sorry:

=INDEX([Rigs_04012019.xlsx]Rigs!$C$2:$C$20,
SUMPRODUCT(([Rigs_04012019.xlsx]Rigs!$A$2:$A$20=B103)*
([Rigs_04012019.xlsx]Rigs!$B$2:$B$20=B104)*
(ROW([Rigs_04012019.xlsx]Rigs!$A$2:$A$20)))-1)

Thanks MARK858

 

[Domenic]

Try...

=INDEX([Rigs_04012019.xlsx]Rigs!$C:$C,MATCH($B$103&"#"&B$104,INDEX([Rigs_04012019.xlsx]Rigs!$A:$A&"#"&[Rigs_04012019.xlsx]Rigs!$B:$B,0),0))

...confirmed with just ENTER.

Hope this helps!