Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



EXCEL Lookup the same value that has multiple values

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 20-04-2012
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

thanks in advance!

Reply With Quote
  #2  
Old 09-06-2012
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),"")
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "EXCEL Lookup the same value that has multiple values"
Thread Thread Starter Forum Replies Last Post
Lookup multiple values in excel Alatar1313 Windows Software 1 09-12-2009 01:49 AM
Excel Look up one value and return multiple corresponding values mellisahi Windows Software 5 27-10-2009 12:40 AM
Excel : vlookup multiple values Laler Windows Software 3 25-06-2009 10:27 AM
Help-How to select multiple values using lookup function??? shivinder Microsoft Project 3 24-03-2009 10:20 PM
Problem in Charting multiple values in Excel Memnon Software Development 2 02-01-2009 07:17 PM


All times are GMT +5.5. The time now is 05:54 PM.