How to fetch data from a sheet, from vertical and horizontal inputs

[Pasadu]

Sir, I have tried using index and match and lookup and sumproduct but i still cannot get the results. I have 2 sheet tabs, where one will pick the RATES from the other table based on 3 conditions (DESTINATION, DIMENSION, AGENCIES).
Thank you in advance sir.

 

[bosco_yip]

Try,

In D2, formula copied down :

=INDEX(Transport!C$2:C$66,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1)



Note : Your "Transport" and "Transport comparision Data" 2 sheets' Dimension column (Col B) appear contents of difference spacing,

So,

In the above 2nd match formula, I am using : MATCH(B2,B$2:B$6,0) to fix the matching order.

Regards

 

[Pasadu]

Try,

In D2, formula copied down :

=INDEX(Transport!C$2:C$66,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1)

View attachment 80934

Note : Your "Transport" and "Transport comparision Data" 2 sheets' Dimension column (Col B) appear contents of difference spacing,

So,

In the above 2nd match formula, I am using : MATCH(B2,B$2:B$6,0) to fix the matching order.

Regards

Thank you sir, but the formula is not correct because in column c, there are other names, not just target rates. The target rates figures are correct but the rates for the other names (Kofi, Ama, Yaa, Kwame) are not correct. I will appreciate it if you could have a review of it.
Thank You.

 

[bosco_yip]

Thank you sir, but the formula is not correct because in column c, there are other names, not just target rates. The target rates figures are correct but the rates for the other names (Kofi, Ama, Yaa, Kwame) are not correct. I will appreciate it if you could have a review of it.
Thank You.

Then,

try this revised formula instead

In D2, formula copied down :

=INDEX(Transport!C$2:Y$66,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1,MATCH(C2,Transport!C$1:Y$1,0))

or,

=OFFSET(Transport!B$1,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1,MATCH(C2,Transport!C$1:Y$1,0))

 

[Pasadu]

Then,

try this revised formula instead

In D2, formula copied down :

=INDEX(Transport!C$2:Y$66,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1,MATCH(C2,Transport!C$1:Y$1,0))

or,

=OFFSET(Transport!B$1,MATCH(A2,Transport!A$2:A$66,0)+MATCH(B2,B$2:B$6,0)-1,MATCH(C2,Transport!C$1:Y$1,0))

Thank you sir, it worked perfectly.

But please, i tried working on the next phase, i encountered a problem. I tried using your formulas and doing the adjustments but i could not get the results. Please on this sheet, I want the answers as well. The data are on the other sheets, you can use anyone as the reference. Thank You.

Sorry I made a mistake in the name of the agency. I know it should match the names on the next sheets. Anyhow, when the right agency name is inserted, I want the automated results. Thank You.

 

[bosco_yip]

Try,

Please use correct name in the "Agency" criteria column as per following example

In C4, formula copied across right and down:

1] Using INDEX+MATCH function

=IFERROR(INDEX('Transport comparision Data'!$E$2:$E$326,MATCH(1,INDEX((LOOKUP("zzz",$A$4:$A4)='Transport comparision Data'!$C$2:$C$326)*($B4='Transport comparision Data'!$B$2:$B$326)*(C$3='Transport comparision Data'!$A$2:$A$326),0),0)),"")

or,

2] Using SUMIFS function

=IFERROR(1/(1/SUMIFS('Transport comparision Data'!$E:$E,'Transport comparision Data'!$C:$C,LOOKUP("zzz",$A$4:$A4),'Transport comparision Data'!$B:$B,$B4,'Transport comparision Data'!$A:$A,C$3)),"")

 

[Pasadu]

Try,

Please use correct name in the "Agency" criteria column as per following example

In C4, formula copied across right and down:

1] Using INDEX+MATCH function

=IFERROR(INDEX('Transport comparision Data'!$E$2:$E$326,MATCH(1,INDEX((LOOKUP("zzz",$A$4:$A4)='Transport comparision Data'!$C$2:$C$326)*($B4='Transport comparision Data'!$B$2:$B$326)*(C$3='Transport comparision Data'!$A$2:$A$326),0),0)),"")

or,

2] Using SUMIFS function

=IFERROR(1/(1/SUMIFS('Transport comparision Data'!$E:$E,'Transport comparision Data'!$C:$C,LOOKUP("zzz",$A$4:$A4),'Transport comparision Data'!$B:$B,$B4,'Transport comparision Data'!$A:$A,C$3)),"")

View attachment 80944

Thank you, thank you, Thank you, thank you, thank you Sir, I appreciate it.