+ Reply to Thread
Results 1 to 2 of 2

Lookup to receive multiple values with INDEX function

  1. #1
    Registered User
    Join Date
    01-22-2020
    Location
    Southampton, England
    MS-Off Ver
    2010
    Posts
    5

    Lookup to receive multiple values with INDEX function

    Hi,

    Trying to look up values from one dataset to another.

    Regular lookup functions only return first matching cell, however matching cell is repeated

    EG. only returns first value, but in first instance colour coded yellow 3 entries are present.
    =IFERROR(LOOKUP(2^15,SEARCH(H4,$A$4:$A$12),$B$4:$B$12),"")

    I need a look up that will return both location and quantity. Have tried playing around with INDEX functions but this returns wrong values in quantity column

    =IF(ISERROR(INDEX($A$4:$B$12,SMALL(IF($A$4:$A$12=$H$4,ROW($B$4:$C$12)),ROW(1:1)),2)),"", INDEX($A$4:$B$12,SMALL(IF($A$4:$A$12=$H$4,ROW($B$4:$C$12)),ROW(1:1)),2))

    Could anyone suggest anything else?
    Feel free to play around with example spreadsheet

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Lookup to receive multiple values with INDEX function

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    there is 1 in i2

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index / Match or other function to lookup 3 values and return column D
    By t83357 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2016, 07:01 PM
  2. Sum Multiple Match Index Lookup Values
    By figo12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2016, 06:15 PM
  3. [SOLVED] Using INDEX(MATCH) with multiple lookup values
    By Wdr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-11-2016, 09:33 AM
  4. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  5. INDEX/MATCH multiple lookup values
    By mythbit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2015, 08:55 AM
  6. Multiple condition lookup for Index function
    By cheal2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2013, 09:13 AM
  7. Lookup 2 possible values within INDEX/MATCH function.
    By Pete123abc in forum Excel General
    Replies: 2
    Last Post: 06-07-2011, 10:51 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1