Excel Lookup

[BenjieLop]

Assume that I have the following data:

(Column A) (Column B) (Column C)

LNAME FNAME CODE

Smith John 123
Jones Michelle 456
Taylor Wilbur 789
White Tom 321
Smith Elaine 654
Brown Robert 987

Let's say, that in Column D, I would like to alphabetize the last name
that appear in Column A (I can do this without any problem).

Furthermore, in Columns E and F, I would like to lookup the FNAME an
CODE that correspond to the alphabetized last names that appear i
Column D.

I can also do this lookup EXCEPT for "Smith". Please note that ther
are two entries for "Smith" and all the lookup values that appear i
Columns E and F for Smith are both "John" and "123."

I cannot find a formula that will allow me to lookup "Elaine" and it
corresponding code "654."

I hope someone out there can help me.

Thanks and regards.


BenjieLo

 

[Domenic]

*Assume that I have the following data:

(Column A) (Column B) (Column C)

LNAME FNAME CODE

Smith John 123
Jones Michelle 456
Taylor Wilbur 789
White Tom 321
Smith Elaine 654
Brown Robert 987

Let's say, that in Column D, I would like to alphabetize the las
names that appear in Column A (I can do this without any problem).

Furthermore, in Columns E and F, I would like to lookup the FNAME an
CODE that correspond to the alphabetized last names that appear i
Column D.

I can also do this lookup EXCEPT for "Smith". Please note that ther
are two entries for "Smith" and all the lookup values that appear i
Columns E and F for Smith are both "John" and "123."

I cannot find a formula that will allow me to lookup "Elaine" and it
corresponding code "654."

I hope someone out there can help me.

Thanks and regards.


BenjieLop *

Hi,

try,

=INDEX($C$2:$C$7,MATCH(1,(($A$2:$A$7="Smith")*($B$2:$B$7="Elaine")),0))

To be entered using CTRL+SHIFT+ENTER.

Hope this helps

 

[Peo Sjoblom]

You can use something like

=INDEX(C2:C20,MATCH(1,(A2:A20="Smith")*(B2:B20="Elaine"),0))

entered with ctrl + shift & enter



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Max]

Perhaps an alternative set-up to experiment with ..

Assume your sample table (Cols A to C) is in Sheet1,
headers in row1, data from row2 down

Put in D2: =A2&COUNTIF($A$2:A2,A2)
Copy D2 down

(This will affix the LNames in col A with a number,
enabling "unique" identification of duplicate LNames, if any)

In Sheet2
-----------
With the same headers in row1

Put in D2: =Sheet1!D2
Copy D2 down

Put in A2:
=OFFSET(Sheet1!$A$1,MATCH($D2,Sheet1!$D:$D,0)-1,COLUMN()-1)
Copy A2 across to C2, then copy down

Now try a sort on col D *only* (select D2x, where x = last data row)
in say, ascending order (or descending)

(I'm guessing this is what you meant by "alphabetizing")

The data in cols A to C will re-arrange to follow the alpha order sorted in
col D

 

[BenjieLop]

Thank you, guys. I will certainly try all your suggestions.

Regards.


BenjieLo

 

[Max]

You're welcome !
Drop us a line here on how it went for you ..