Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    INDEX-MATCH (EXCEL 2003)

    Edited by HansV to present data in table format

    what modifivations i need to do in my index match formula "=INDEX($f$2:$f$2875,MATCH(1,($e$2:$e$2875=A2)*($g $2:$g$2875>=B2)*($g$2:$g$2875<=C2),0))" to allow including multiple REF names if their MARK values fall between ID TOP & BOT values.

    <table border=1><tr><td>ID</td><td>TOP</td><td>BOT</td><td>REF</td><td align=right>
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    Could you explain your question in more detail? I don't understand it.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    Are you after this in D2 the ref column(copy it down the column):
    =INDEX($G$2:$G$8,MATCH(B2,$H$2:$H$8)+1)

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><tr><td align=center valign=bottom>1</td><td valign=bottom>ID</td><td valign=bottom>TOP</td><td valign=bottom>BOT</td><td valign=bottom>REF</td><td align=right valign=bottom>

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    Hi HansV,
    a sample of my data is in the attached file. what i am trying to do is use index-match formula(or min& max or any other formula) to return all REF names that falls between the TOP & BOT values using the MARK values. the formula works when there is only one MARK value that satisfy the formula conditions, as in the second row. I want it to output all REF names when mutiple MARK values satisfy the formula conditions. for example for the first TOP & BOT values of 7089&7102 it should return the names ABD1&AD2A.

    i hope i made it clear.
    TIA
    dubdub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    I still don't understand. The MARK values for ABD1 and AD2A are 7074 and 7080. Neither is in the interval from 7089 to 7102. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    minor correction:
    assume the first row TOP value is 7070 instead of 7089.
    TIA
    dubdub

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    hi steve,sorry for the late reply,
    not exactly. Pls. follow my replies to HAnsV.
    TIA
    dubdub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    You can use this custom function:

    Function MultiMatch(id As Long, lo As Long, hi As Long, _
    rng As Range, Optional sep As String) As String
    Dim i As Long
    For i = 1 To rng.Rows.Count
    If rng.Cells(i, 1) = id And rng.Cells(i, 3) >= lo And _
    rng.Cells(i, 3) <= hi Then
    MultiMatch = MultiMatch & sep & rng.Cells(i, 2)
    End If
    Next i
    If InStr(MultiMatch, sep) = 1 Then
    MultiMatch = Mid(MultiMatch, Len(sep) + 1)
    End If
    End Function

    In cell D2, enter the formula

    =MultiMatch(A2,B2,C2,$F$2:$H$8,"&")

    and fill down. See attached version.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    thanks HansV, highly appreciate the time.

    but still there are many blanks and my objecttive is not met.
    i want you to think of each REF name as a subsurface layer that starts at a depth(MARK value) and it has a thickness that extend to the next REF name depth(MARK value)and TOP&BOT values are completion intervals. the objective is to find the layers (REF names) completed by eachTOP&BOT values/depths.
    TIA
    dubdub

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    Is this modification to Hans' code what you are after?

    <pre>Function MultiMatch2(id As Long, lo As Long, _
    hi As Long, rng As Range, Optional sep As String) As String
    Dim i As Long
    For i = 1 To rng.Rows.Count
    If rng.Cells(i, 1) = id And rng.Cells(i, 3) <= hi _
    And rng.Cells(i + 1, 3) >= lo Then
    MultiMatch2 = MultiMatch2 & sep & rng.Cells(i, 2)
    End If
    Next i
    If InStr(MultiMatch2, sep) = 1 Then
    MultiMatch2 = Mid(MultiMatch2, Len(sep) + 1)
    End If
    End Function</pre>


    Change the formula in D1 to:
    <pre> =MultiMatch2(A2,B2,C2,$F$2:$H$8,"&")</pre>


    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    It's difficult if you keep changing the problem. This is quite different from your original description.Could you give an example? E.g. in row 4 in the spreadsheet you attached, the TOP and BOT values are 7147 and 7165. Which REF names would you like to be returned for this row, and why?

  12. #12
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    hi steve,

    i have tested it in part of the data I have and so far it's what i am after. i dont know what happened when I insert a column in the range $F$2:$H$8, the returns data disappear. any idea?
    TIA
    dubdub

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    hi HansV,
    it suppose to give me AD2B. The reason is AD2B MARK value starts at 7118 and ends at the start of next REF name AD3A of 7187.
    TIA
    dubdub

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    The code expects ID to be the 1st column of rng: Cells(i, 1) = id. If ID is in another column of rng, change the 1 accordingly.
    The code expects MARK to be the 3rd column of rng: rng.Cells(i, 3) <= hi And rng.Cells(i + 1, 3) >= lo. If MARK is in another column ot rng, change the 3 accordingly.
    The code expects REF to be the 2nd column of rng: MultiMatch2 = MultiMatch2 & sep & rng.Cells(i, 2). If REF is in another column of rng, change the 2 accordingly.

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: INDEX-MATCH (EXCEL 2003)

    Many thanks HansV.
    TIA
    dubdub

Posting Permissions

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