Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Index/Match across different sheets

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index/Match across different sheets

    This is probably an easy question for most of you, but it's killing me. I tried searching before I posted this, but the suggestions given didn't seem to work for me.

    I need to look up data from a cell on one Excel sheet, compare it to a list on a 2nd sheet and when they equal, take a value from a different column on the 2nd sheet and drop it back on the 1st sheet. Since that's a horrible explanation, let me try explaining it this way:

    Sheet 1
    Column A has a long list of code type 1s
    Column E has a long list of code type 2s

    Sheet 2
    Cell C2 has code 1
    Cell E2 needs code 2

    I know it should take an index/match function, but nothing I try is working. What I'm trying is the following formula in Cell E2 of sheet 2:

    =INDEX('Sheet 1'!A:A,MATCH(C2,'Sheet 1"!E:E,0),5)

    But all I get is no result. What's driving me bonkers is that if I copy everything over to a single sheet, that formula works fine.

  2. #2
    Board Regular
    Join Date
    Aug 2009
    Location
    london uk
    Posts
    824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match across different sheets

    =INDEX(Sheet1!E:E,MATCH(C2,Sheet1!A:A,0))
    or
    =VLOOKUP(C2,Sheet1!A:E,5,FALSE)

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match across different sheets

    Welcome to the board...

    here's the basic syntax

    =INDEX(ReturnRange,MATCH(LookupValue,SearchRange,0))

    According to your explaination..
    ReturnRange is 'Sheet 1'!E:E (the range you want to return a value from)
    LookupValue is 'Sheet 2'!C2 (the value you want to find a match for)
    SearchRange is 'Sheet 1'!A:A (the range to search for the lookupvalue)

    So try this in Sheet 2 E2

    =INDEX('Sheet 1'!E:E,MATCH('Sheet 2'!C2,'Sheet 1'!A:A,0))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Jul 2009
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match across different sheets

    I am trying to do something similar however when I am putting in the formula it is saying... "We found a problem with this formula". The formula I am using is =INDEX(Sheet3!D:D;MATCH(Aging!A2&Aging!C2;Sheet3!G:G&Sheet3!F:F;0)).

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match across different sheets

    how would you do this so that it may find multiple matches rather than the first one only?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •