Need assistance with formula

[pujo]

Morning all.

I cannot seem to figure out the proper syntax for a formula.

I have a named range "Policy_Number" that is A1:A50
In B1:B50 I have the actual policy.

So, on another sheet I am trying to reference the policy using the policy number and getting the policy from B1:B50 adjacent to the named range "Policy_Number"

Here is what I have so for,

=IF((Policy_Number)=L$6,Offcet(INDIRECT(ADDRESS(ROW(), COLUMN())),0,0,0,1))

The first part of the formula is working in reference to L$6, but cant get the second part of the formula to work.

Please assist.

Thanks,
-PuJo

 

[jorismoerings]

Hi,

Don't know if i understood correctly but think using HLOOKUP would be better fit for purpose.
Haven't tried your formula but the typo in using "Offcet" instead of "Offset" pops up straight away.

 

[pujo]

No luck with HLOOPUP

Matching value from a named range returning the value in the cell to the right of the match.

Any ideas?

 

[Marcelo Branco]

Maybe...

=INDEX(OFFSET(Policy_Number,,1),MATCH(L$6,Policy_Number,0))

Hope this helps

M.

 

[pujo]

That got it, thanks.
-PuJo