I tried again and didn't seem to be wrong to me. Since I am assuming
row1 to be your header, so there is ROW()-1 in my formula. If row1 is
where your data start, then replace "ROW()-1" with "ROW()".
ie.
A1 =
INDEX(Master!$A$1:$D$5,SUMPRODUCT(SMALL(ROW(Master !$1:$5)*(Master!$B$1:$B$5="Location
A"),COUNTA(Master!$B$1:$B$5)-COUNTIF(Master!$B$1:$B$5,"Location
A")+ROW())),COLUMN())
I've also attached my file(without header).
Hope it helps.
+-------------------------------------------------------------------+
|Filename: Choose.zip |
|Download:
http://www.excelforum.com/attachment.php?postid=3657 |
+-------------------------------------------------------------------+
--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile:
http://www.excelforum.com/member.php...fo&userid=7094
View this thread:
http://www.excelforum.com/showthread...hreadid=390438