Help with conditional vlookup

[Nicolas Tapia]

Hi all,

I have a territory code which can be seen by different sales rep (SR)(depending of the pillar). So a single territory code, let's say "terr1" could be seen by SR 1, from pillar A, SR 2, from pillar B, and so on.

Some of these SR are not yet hired, so I have a column with the values "on board" and "TBH".

The first table has 3 columns, column A "Territory", column b "SR Name Pillar A", Column c "SR Name Pillar B". I need to get column b and c (the names).

The second table has 4 columns. Each sales rep is in individual rows. The column A "Territory", Column b "SR Name", column c "Pillar", Column d "Hire status".

I have to make a conditional look up, but I am not quite sure of how. I need to look for the Territory code, then to ensure the Pillar that I am searching, and after that, ensuring that the SR is "on board", after all of before is true, I need the SR name (e.g. Joe).

I tried a formula like this:
=IF(AND(Hired_status="on board", pilar="A"),Vlookup(territory,data,2,0)
but it ony works in certain cases.

I uploaded an example file. In reality I have 103 different territories, 4 pillars, and 300 Sales reps.

Could you help me with this?
I hope I made myself clear

Thanks a lot

Nicolas

 

[chirayu]

Vlookup only brings up the first result that matches the criteria. It cannot bring up multiple results. I don't know of a formula solution for this but macro solution is easy enough.

 

[NARAYANK991]

Hi ,

It would help if you could manually fill in the outputs , so that it is clear what the formula is expected to do.

Narayan