Index match matches the wrong column

[Jonathan9]

Hi,

I'm doing an exercise where I ranking countries according values. I've attached the workbook. I'm using an index match match formula for this, but for some reason it's always returning the value from the column next to where it should return the value. I can't seem to figure this out...

And I need to do a formula that first finds a match in one row and then finds the highest value in that column then returns the county on that row. It's highlight in yellow where the formulas need to go.

Any help on this would be greatly appreciated.

Thanks.

[Richard Buttrey]

Hi,

It's not clear, at least to me what numbers you want. Manually add some typical results and explain with a note which sheet/cells produce these results.

[canapone]

Hi,

a small correction


=INDEX('Bilateral Remittance Matrix'!$N$3:$HS$218,MATCH(C4,'Bilateral Remittance Matrix'!$N$3:$N$218,0),MATCH(F4,'Bilateral Remittance Matrix'!$O$2:$HS$2,0))

Same values from:

=SUMPRODUCT('Bilateral Remittance Matrix'!$N$3:$HS$218,('Bilateral Remittance Matrix'!$N$3:$N$218=C4)*('Bilateral Remittance Matrix'!$N$2:$HS$2=F4))


Regards

[Jonathan9]

Hi,

It's not clear, at least to me what numbers you want. Manually add some typical results and explain with a note which sheet/cells produce these results.

I have now uploaded a version with pasted values of the data I need returned. I hope it's clearer now.

@canapone - thanks for your input. But I'm not sure if I entirely understand. I did use the N reference and I tried the sumproduct formula but for some reason it doesn't give the right matches. It's like it's working with some hidden fields, even though there are none.

[canapone]

Hi,

please see the "green" sumproduct" and "index" in the attached file.

Hope it's a little step forward