General location and Restrict location

[RZoig]

Hello,

I've searched several threads but I couldn´t find one that could help.

I'm trying to find a formula and a VBA function that is able to return the location of a string in a sheet (A) and also within a restricted area (B).

This is what I want for each Output (A and B) with 16.03 for Reference Input:

[TABLE="width: 421"]
<tbody>[TR]
[TD="colspan: 3"][TABLE="width: 421"]
<tbody>[TR]
[TD="colspan: 3"]A. General reference[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8.00[/TD]
[TD]8.30[/TD]
[TD]8.15[/TD]
[TD]8.05[/TD]
[TD]9.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12.05[/TD]
[TD]12.15[/TD]
[TD]12.10[/TD]
[TD]12.20[/TD]
[TD]13.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14.00[/TD]
[TD]14.02[/TD]
[TD]14.05[/TD]
[TD]14.08[/TD]
[TD]14.40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]16.01[/TD]
[TD]16.05[/TD]
[TD]16.03[/TD]
[TD]15.59[/TD]
[TD]16.20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]18.07[/TD]
[TD]18.10[/TD]
[TD]18.08[/TD]
[TD]18.15[/TD]
[TD]17.56[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20.02[/TD]
[TD]20.12[/TD]
[TD]20.20[/TD]
[TD]20.10[/TD]
[TD]21.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REF->[/TD]
[TD]16.03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Output ->[/TD]
[TD]C4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]B. Restricted reference[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]4A[/TD]
[TD]5A[/TD]
[TD]6A[/TD]
[/TR]
[TR]
[TD]PHA1[/TD]
[TD]8.00[/TD]
[TD]8.30[/TD]
[TD]8.15[/TD]
[TD]8.05[/TD]
[TD]9.00[/TD]
[/TR]
[TR]
[TD]PHA2[/TD]
[TD]12.05[/TD]
[TD]12.15[/TD]
[TD]12.10[/TD]
[TD]12.20[/TD]
[TD]13.00[/TD]
[/TR]
[TR]
[TD]PHA3[/TD]
[TD]14.00[/TD]
[TD]14.02[/TD]
[TD]14.05[/TD]
[TD]14.08[/TD]
[TD]14.40[/TD]
[/TR]
[TR]
[TD]PHA4[/TD]
[TD]16.01[/TD]
[TD]16.05[/TD]
[TD]16.03[/TD]
[TD]15.59[/TD]
[TD]16.20[/TD]
[/TR]
[TR]
[TD]PHA5[/TD]
[TD]18.07[/TD]
[TD]18.10[/TD]
[TD]18.08[/TD]
[TD]18.15[/TD]
[TD]17.56[/TD]
[/TR]
[TR]
[TD]PHA6[/TD]
[TD]20.02[/TD]
[TD]20.12[/TD]
[TD]20.20[/TD]
[TD]20.10[/TD]
[TD]21.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REF->[/TD]
[TD]16.03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Output ->[/TD]
[TD] 4A.PHA4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So:
In 'A' I want the location of 16.03 in a Excel sheet (C4 in my example)
In 'B' I want references of 16.03 within a table separated with a '.' (4A.PHA4 in my example).

A formula and a VBA function solution would be super great, but only the formula would do nicely!

Thank You
Rui

 

[stunnrock]

Could the reference value appear more than once in the data?

 

[stunnrock]

A:

=if(countif($a$1:$e$6,$f$9)<>1,"-",char(code("A")-1+sumproduct(column($a$1:$e$6)*($a$1:$e$6=$f$9)))&sumproduct(row($a$1:$e$6)*($a$1:$e$6=$f$9)))

B:

=if(countif($b$2:$f$7,$f$9)<>1,"-",index($1:$1,sumproduct(column($b$2:$f$7)*($b$2:$f$7=$f$9)))&"."&index($a:$a,sumproduct(row($b$2:$f$7)*($b$2:$f$7=$f$9))))

 

[RZoig]

Stunnrock,

For the time being let´s suppose we're dealing with unique records, i suppose.

Code B:
Fantastic. I'm still depicting what you've done, but it works fine.

Code A:
Something's missing or I adjusted poorly (I use ';' in my regional settings instead of ',') . Result was a minus symbol (-).
No 'C4' as output.

Thanks!
Could you help me a little bit more with A?

 

[RZoig]

Stunnrock

Code A:
Looked again and "or I adjusted poorly" is the right answer. So, amazing. Thank you! It worked of course.

Now I'll have to find how SumProduct is related to find strings! I use it only for calculations not as a reference tool!
How Odd!!

Thanks again!
Rui