Using VLOOKUP, IF and RIGHT functions together

[Alicia]

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

 

[Pete_UK]

It looks as if you are on the right track, to me. The correct syntax
would be:

=IF(RIGHT(A2,3)="HPT",VLOOKUP(cell with P&L#,Security!PTSec,2,FALSE),
IF(RIGHT(A2,3)="HFT",VLOOKUP(cell with P&L#,Security!FTSec,2,FALSE),
IF(RIGHT(A2, 1)="C",VLOOKUP(cell with P&L#,Security!CSec,2,FALSE),"")))

This is all one formula - I've broken it to try to avoid awkward line
breaks when posting.

I assume that you know the cell reference for "cell with P&L#", and
also assume that PTSec, FTSec and CSec (my invention) are named ranges.
You didn't state what you wanted if none of the three conditions were
met, so I've made it return a blank - if there is not a match in any of
the lookups you will get #N/A error.

Hope this helps - I don't know about being a "great one" (they're all
asleep now!).

Pete

 

[Dave Peterson]

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]

Pete,
I will try this as soon as I can get a free minute! That looks like exactly
what I'm looking for. I just couldn't seem to get the syntax right. I'll
let you know how it goes!

Thanks, Alicia

P.S.: Assuming this works, you are officially promoted to "Great One"