extract values based on variables which is user input

[swetasingh]

Hi, I have created a variable which captures phone number based on user input. 

based on user input i want to extract name of the person from phone number and use that name to extract pin code of the person from a different table. 

Table 1 : 

Ph No	Name
97xxxxxxx16	AB
84XXXXXX24	AA
34xxxxx99	AC

Tab 2 : 

Name	Pin code
AA	4896
AB	4434
AC	1234

I have defined a variable which is currently giving me the phone no, but when i use the same expression in set analysis gives no result. 

if i enter name of the person directly in the set it works but i have huge data and i want to make it dynamic. 

Below are the formulas : 

Avg({$< [Name]={'AB'}>}Pin code) - gives correct pin code

Avg({$<Name = {if( [ph no]=$v,Name}>} Pin code)

Please provide resolution. 

[dplr-rn]

try below
=Avg({$< [Ph No]={$(var)}>}[Pin code])

[swetasingh]

Hi thanks this expression works when there are only these tables, but with more table it is screwing up other values .. is there way when I can extract name based on phone number input n save it in a variable and then use that variable to get the values ????

i tried using below it didn’t work : 

if ( [ph no] = $v, Name, 0)

[dplr-rn]

Not sure how it can screw up the values if the name is associated with a phone number.
i cant see how saving it into a variable is very different from what i suggested but as you know your data better.
try this as new variable
Only({$< [Ph No]={$(v)}>}Name)
note that this will give null if no phone number is inputted so your final expression using this variable should take that into account