Results 1 to 2 of 2

Thread: EXCEL Lookup the same value that has multiple values

  1. #1
    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!

  2. #2
    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),"")

Similar Threads

  1. Lookup multiple values in excel
    By Alatar1313 in forum Windows Software
    Replies: 1
    Last Post: 09-12-2009, 02:49 AM
  2. Replies: 5
    Last Post: 27-10-2009, 12:40 AM
  3. Excel : vlookup multiple values
    By Laler in forum Windows Software
    Replies: 3
    Last Post: 25-06-2009, 10:27 AM
  4. Help-How to select multiple values using lookup function???
    By shivinder in forum Microsoft Project
    Replies: 3
    Last Post: 24-03-2009, 10:20 PM
  5. Problem in Charting multiple values in Excel
    By Memnon in forum Software Development
    Replies: 2
    Last Post: 02-01-2009, 08:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,695,179.70305 seconds with 17 queries