Maybe another option...

=vlookup(a1,indirect("security!" & mid(b1,2,2) & "sec"),2,false)

Where A1 is the cell with the P&L# and B1 is the cell that contains HPT*, HFT*,
....

Alicia wrote:

Oh great ones,
Where to begin. I want to combine IF to test what RIGHT returns and based
on the answer, point it to VLOOKUP to return a cell content (which is a long,
complicated path).

In English, IF (right(cell, 3) returns "HPT", then VLOOKUP (cell with P&L#,
Security!PTSec,2, FALSE), (if (right(cell,3) returns "HFT", then Vlookup
(cell with P&L#, Security!FTSec, 2, FALSE), IF (right(a2, 1) returns C... etc.

I want to test for HPT, HFT and C to return separate strings and everything
else should return the same string. I'm thinking there's a way to nest IF,
RIGHT and VLOOKUP to achieve this but I can't seem to make it work. I've
seen Index and Match (which I've never used) which might be the way to go but
the biggest issue I have is I need to figure out if they are PT, FT or C from
the end of a string.

Please let me know if you need further detail to be able to assist me. This
kind of weird nesting is definitely not in any book I've ever seen and I've
searched everywhere I can think of to find something I can use.

Thanks, Alicia

--

Dave Peterson