+ Reply to Thread
Results 1 to 7 of 7

Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

  1. #1
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    I have part numbers in sheet 1, column A

    I have new part number in sheet 2, column A and X references in columns F thought to Z

    I need to return the part number that exists in sheet 2 that has a matching part number in the cross reference.

    I just cannot get similar index match formulas to work!

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    What's £20 worth nowadays, in US$...

    E4 is an array formula (Ctrl-Shift-Enter).


    x ref example.xlsx

  3. #3
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    What's a dollar worth nowadays! I should have offered a gram or two of gold!

  4. #4
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    Quote Originally Posted by jhren View Post
    What's £20 worth nowadays, in US$...

    E4 is an array formula (Ctrl-Shift-Enter).


    Attachment 238959
    Problem SOLVED! Man is a genius - What's your ppal please?

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    My interpretation is that you want a formula to return the results you show in C4 and C5, is that right? To do that with a single formula try this formula in C4

    =IFERROR(INDEX(Sheet2!A$2:A$100,SMALL(IF(Sheet2!F$2:Z$100=A4,ROW(Sheet2!F$2:Z$100)-ROW(Sheet2!F$2)+1),1)),"No match")

    confirmed with CTRL+SHIFT+ENTER and copied down
    Audere est facere

  6. #6
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    Ahhh, it doesn't work when I move it to my master sheet! I'll upload it again and perhaps someone can tweek it?

  7. #7
    Registered User
    Join Date
    01-05-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem

    Found the issue - there cannot be any #NA in x refs!

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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