Determining Location of Value in X and Y coordinates.

[hreyo25]

Hello I have a matrix set up and I am need help finding the right code that would be able to find a specific cell value and display where in the matrix that value is. Attached is a sample of this problem. I placed the the different names throughout the matrix and in the end would like to determine a score for these value by multiply the X value by the Y value. Any help will be appreciated.

-Cheers

 

[Sam Mathai Chacko]

X=SUMPRODUCT($E$31:$AH$31*($E$1:$AH$30=$A36))
Y=SUMPRODUCT($D$1:$D$30*($E$1:$AH$30=$A36))

 

[Sajan]

Hello @hreyo25,
Not sure if you are looking for VBA code that does this, but here is a formula you can use:

In cell B36, put the following array formula and copy down:
=MAX(($E$1:$AH$30=$A36)*$E$31:$AH$31)

In cell C36, put the following array formula and copy down:
=MAX(($E$1:$AH$30=$A36)*$D$1:$D$30)

in cell D36, put the following regular formula and copy down:
=B36*C36

Cheers,
Sajan.

 

[hreyo25]

Sajan,

When I copy your formulas in I am receiving a VALUE error. What do you think can be causing this?

Thanks

 

[Sam Mathai Chacko]

hreyo25, they are called CSE (CTRL+SHIFT+ENTER) formulas or array formulas. Note that Sajan had clearly mentioned this here

...put the following array formula and...

 

[Sam Mathai Chacko]

Having said that, did you try my formula? That you don't need to enter as a CSE formula.

 

[Sajan]

Hi @hreyo25,
As Sam pointed out, array formulas are special formulas that need to be entered with Ctrl + Shift + Enter, instead of just the Enter key. You would be able to confirm the array formula by noticing that the formula becomes enclosed in {}

Cheers,
Sajan.

 

[hreyo25]

Thank you both so much for your help both ways work just great. Thanks!

 

[Sajan]

Glad to help! Welcome back anytime.