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
thanks in advance!
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),"")