Index/Match

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

Thread: Index/Match

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index/Match

     
    Need some help with indexMatch.


    I have two worksheets (Sameworkbook)
    worksheet 2 has the data that I need to look at example

    Col. C:
    Joe
    Mark
    Bill

    Now, match the names above to those in worksheet 1 and return the value in col. A of worksheet 1 to col.B of worksheet 2

    note: worksheet 1has the same names I just need to get its matching value.

    I was told index/match might be able to do this.

    can someone give me a start?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Quote Originally Posted by Lester601
    Need some help with indexMatch.


    I have two worksheets (Sameworkbook)
    worksheet 2 has the data that I need to look at example

    Col. C:
    Joe
    Mark
    Bill

    Now, match the names above to those in worksheet 1 and return the value in col. A of worksheet 1 to col.B of worksheet 2

    note: worksheet 1has the same names I just need to get its matching value.

    I was told index/match might be able to do this.

    can someone give me a start?
    Where are the names on Sheet1 -- in B perhaps?

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Hi Lester601:

    It is not very clear from your description what you are trying to accomplish -- so bear with me, I am going to make some assumptions about how your data is laid out and what you are trying to get. Let us say the names in worksheet1 are laid out as in ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Bill
    2
    Joe
    3
    Mark
    4
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    then let us say the names in worksheet2 are laid out as shown in cells C1:C3 as depicted in ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    2Joe
    2
    3Mark
    3
    1Bill
    4
    Sheet2

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Then I assume you are trying to find the row number in sheet1 of the matching name in sheet2 -- these are presented in column B -- meaning the name Joe is is row2 (cell A2) in worksheet1, and similarly Mark is in row3 and Bill is in row1

    I hope this helps. If I have misunderstood your question -- my apologies!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Thanks for your reply, worksheet1 has may cols. with all the names in worksheet2.

    Worksheet1, using your example books, has data in colum C for each name. Example "2A"


    From workbook2:

    I want the fomula to match each name in workbook 2 with workbook 1 and return the value in Col. C of workbook 1.

    The value will be place in col B sheet2 as per work book example.


    I hope this is a little clearer

    Thanks

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Quote Originally Posted by Lester601
    Thanks for your reply, worksheet1 has may cols. with all the names in worksheet2.

    Worksheet1, using your example books, has data in colum C for each name. Example "2A"


    From workbook2:

    I want the fomula to match each name in workbook 2 with workbook 1 and return the value in Col. C of workbook 1.

    The value will be place in col B sheet2 as per work book example.


    I hope this is a little clearer

    Thanks
    Please post sample data in worksheet1, and worksheet2, and show what are your expected results -- and then let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Quote Originally Posted by Lester601
    Thanks for your reply, worksheet1 has may cols. with all the names in worksheet2.

    Worksheet1, using your example books, has data in colum C for each name. Example "2A"


    From workbook2:

    I want the fomula to match each name in workbook 2 with workbook 1 and return the value in Col. C of workbook 1.

    The value will be place in col B sheet2 as per work book example.


    I hope this is a little clearer

    Thanks
    It seems you're easily distracted. You also seem to confound the words "workbook" and "worksheet".

    You have names in worksheet Sheet2: In which column?

    You have names in worksheet Sheet1: In which column?

    It looks like you want to match a name from Sheet2 to the names in Sheet1. And if the match succeeds, you want corresponding value from column C on Sheet1 to Sheet2. Right?

    Now would you please answer the questions above? If the questions depict a wrong picture of the your problem, try to re-describe the problem you want to solve.

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    First let me apologize, I certainly meant worksheets 1 and 2.

    The names are in Col B of worksheet1

    COl B
    Bill
    Joe
    Mark

    Col A
    2A
    2B
    2C


    Worksheet 2
    Col C
    Bill
    Joe
    Mark

    Column B
    return value from sheet 1 column A for each match (2A)
    return value from sheet 1 column A for each match (2B)
    return value from sheet 1 column A for each match (2C)


    Sorry for confusing this I am just very tired..

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

    Quote Originally Posted by Lester601
    First let me apologize, I certainly meant worksheets 1 and 2.

    The names are in Col B of worksheet1

    COl B
    Bill
    Joe
    Mark

    Col A
    2A
    2B
    2C


    Worksheet 2
    Col C
    Bill
    Joe
    Mark

    Column B
    return value from sheet 1 column A for each match (2A)
    return value from sheet 1 column A for each match (2B)
    return value from sheet 1 column A for each match (2C)


    Sorry for confusing this I am just very tired..
    =INDEX(Sheet1$A$2:$A$100,MATCH(C2,Sheet1!$B$2:$B$100,0))

    Adjust the ranges where needed.

  9. #9
    Board Regular
    Join Date
    Apr 2003
    Posts
    164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index/Match

      
    Thanks for your help. Its working

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
  •  

 

 
DMCA.com