Lookup formula problemo

V

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.
 
C

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
 
V

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top