Are double lookups possible ? 1

[LeighAnne]

Here's the thing ! Column D has a list of names (the same name may appear several times, maximum 6 so far), Column J has an internal code and Column S has a percentage value.

How can I look up a name in column D, and if the internal code is the same as the one I'm dealing with then I want to return the percentage value.

I've tried using OFFSET, MATCH, INDEX and VLOOKUP in varying ways but can only get halfway there.

The following is the closest I've got but in doesn't take into account the internal reference, it just looks up the n'th recorrence of the name (in this case the last, found by using COUNTIF):

=INDEX($D$2:$S$400,SMALL(IF($D$2:$S$400=A22,ROW($D$2:$S$400)-ROW($D$2)+1,ROW($D$400)+1),COUNTIF($D$2:$S$400,A22)),16)

NOTE:
A22 is the name I'm looking up ! And these are array forumlas (i.e. so you must press Ctrl+Shift+Enter when you edit the formula to get the curly brackets).

Any help would be useful, else I might be testing whether or not laptops can survive when thrown from the third floor of a building !!!!!


Leigh-Anne

 

[dsi]

You may want to consider Auto Filters or Advanced Filters. Just do a search in Excel help for some examples.

 

[Hasit]

I think I understand what you are after. Have you consider nested "IF"s??

For example:

=IF(D1="Hasit",IF(J1=1,S1,0))

Where J1=1 is looking for the internal code (set to 1).

If both conditions match, then it returns the percentage in S1.

The only thing that confuses me is the multiple (same) names. If I could see an example of your worksheet, I may be able to add more detail....

 

[SkipVought]

Hi, LeighAnne,

Hang on for the ride on this one...

With you data on Sheet1, I have a 3 x 4 matrix set up as follows on a Sheet2....

  A         B                   C   
1 LookupVal 0                   1
2 name1     =MATCH(A2,Name,B1)  =MATCH(A2,Name,C1)
3 code1     =MATCH(A3,INDIRECT("Sheet1!B"&B2+1&":B"&C2+1),B1)
4           =INDEX(Value,B2+B3-1,1)

Either name your name column, "Name" or insert the absolute reference for name
Either name your percent value column, "Value" of insert the absolute reference for percent value
You enter the name in A2 and the code in A3. Your answer is in B4.

Hope this works for you Skip,

 

[SkipVought]

oops!!!!!!

the formula in B# should be...

=MATCH(A3,INDIRECT("Sheet1!J"&J2+1&":J"&J2+1),B1)

Sorry :-( Skip,

 

[SkipVought]

OOps,OOPs

B3, not B#

Where is my mind?????? Skip,

 

[LeighAnne]

SkipVought,

Thanks for your help ! I never would have thought of your solution !

I'ts absolutely perfect !!!!!

Leigh-Anne

P.S. Big Star From Me !!!