Lookup_returning any value..

[Aline]

The formula on the E1 is =LOOKUP(2,1/(A1:A20=D1),B1:B20)

A B C D E
Bill Science Bill
Bill Cathy
Bill Dan Math
Cathy John
Cathy Math Mary Science
Cathy
Dan
Dan
Dan Math
John Religion
John
Mary
Mary Science


How can I modify the formula so it will return whatever value it has on
column B:

A B C D E
Bill Science Bill Science
Bill Cathy Math
Bill Dan Math
Cathy John Religion
Cathy Math Mary Science
Cathy
Dan
Dan
Dan Math
John Religion
John
Mary
Mary Science

Thanks,
Aline

 

[Max]

I've posted responded further to you in your earlier thread, re:
http://tinyurl.com/5wbb9a

Don't give up so easily on the array formula suggestion!

To ease you into your comfort zone on the array alternative,
here's a working sample to illustrate it,
and its based on the setup that you posted here:

http://freefilehosting.net/download/3m0k1
Multi-Criteria Index n Match.xls

The descriptives:
Array-enter* into E1, then fill down:
=INDEX(B$1:B$13,MATCH(1,(A$1:A$13=D1)*(B$1:B$13<>""),0))

*To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik