Lookup formula problemo

[Vacuum Sealed]

Hi All

Can anyone have a look at this and put it into the right syntax please....

'=IF(A2="","",IF(A2="Company",VLOOKUP(B2,Lookups!$A$2:$D$98,2,IF(A2="Subbie",VLOOKUP(B2,Lookups!$A$101:$D$198,2,IF(A2="Agency",VLOOKUP(B2,Lookups!$A$201:$D$298,2,FALSE))))))).

I'm not sure if I should be using Match | Index or what.

Column A is a Data Validated lookup with 3 choices:

Company, Subbie, Agency

Column B is the filtered result of Column A

So If I select Company - Column A and John Doe - Column B, I would like the
above to return his details in Column C, which is where the above is
located.

Each Group has its own lookup range.

It would probably be easier if I named the ranges and referred to them
directly, but as yet, I haven't dealt with that before so I'm somewhat
hesitant...

TIA
Mick.

 

[Claus Busch]

Hi Mick,

Am Sat, 4 Jun 2011 23:11:42 +1000 schrieb Vacuum Sealed:

'=IF(A2="","",IF(A2="Company",VLOOKUP(B2,Lookups!$A$2:$D$98,2,IF(A2="Subbie",VLOOKUP(B2,Lookups!$A$101:$D$198,2,IF(A2="Agency",VLOOKUP(B2,Lookups!$A$201:$D$298,2,FALSE))))))).

array formula (STRG+Shift+Enter):
=IF(A2="","",INDEX(Lookups!C:C,MATCH(A2&B2,Lookups!$A$1:$A$300&Lookups!$B$1:$B$300,0)))


Regards
Claus Busch

 

[Vacuum Sealed]

Hi Claus

Thx for your help and replying..

I seem to have made a mountain out of a mole hill in that I made the whole
thing quite complicated when in actual fact I was looking at it all wrong...

I fixed it and its all good thx...

=IF(A2="","",VLOOKUP(B2,Lookups!$A$2:$B$300,2,FALSE))

Cheers
Mick.