# Thread: EXCEL Lookup the same value that has multiple values

1. Member
Join Date
Apr 2012
Posts
1

## EXCEL Lookup the same value that has multiple values

been stumped on this one and trying to find a solution:

I have 2 rows of data.

1: 14 14 12 11 10 5
2: 123 122 119 118 115 111

need to lookup 14 and return 123 in one cell and 122 in the next cell. all i can do now is 123 123.

any help or direction would be appreciated. i am able to do this if the data is vertical, but can't convert it to horizontal for some reason.

here is the code i used when the data was vertical:
=INDEX(\$b\$1:\$b\$15,SMALL(IF(\$a\$1:\$a\$15=I21,ROW(\$a\$1:\$a\$15)-MIN(ROW(\$a\$1:\$a\$15))+1,""),COLUMN(\$B\$1)))) ctrl+shift+enter

2. Member
Join Date
Jan 2006
Posts
605

## Re: EXCEL Lookup the same value that has multiple values

I think that the following formulas would work:

H1:
=VLOOKUP(G1,Tbl,2,0)

H2 [array formula]:
=IF(COUNTIF(INDEX(Tbl,0,1),G\$1)>ROW()-ROW(H\$1),
OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G\$1,ROW(Tbl)-CELL("Row",Tbl)),
ROW()-ROW(H\$1)+1),1,1,1),"")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•