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:

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

<tbody>
</tbody>

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