Results 1 to 15 of 15
Thread: INDEXMATCH (EXCEL 2003)

20060306, 07:33 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
INDEXMATCH (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

20060306, 07:33 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: INDEXMATCH (EXCEL 2003)
Could you explain your question in more detail? I don't understand it.

20060306, 09:48 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: INDEXMATCH (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>

20060306, 10:02 #4
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (EXCEL 2003)
Hi HansV,
a sample of my data is in the attached file. what i am trying to do is use indexmatch 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

20060306, 10:21 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: INDEXMATCH (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>

20060306, 10:21 #6
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (EXCEL 2003)
minor correction:
assume the first row TOP value is 7070 instead of 7089.TIA
dubdub

20060306, 10:53 #7
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (EXCEL 2003)
hi steve,sorry for the late reply,
not exactly. Pls. follow my replies to HAnsV.TIA
dubdub

20060306, 11:16 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: INDEXMATCH (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.

20060306, 12:05 #9
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (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

20060306, 12:25 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: INDEXMATCH (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

20060306, 12:30 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: INDEXMATCH (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?

20060307, 04:44 #12
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (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

20060307, 04:56 #13
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (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

20060307, 05:46 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: INDEXMATCH (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.

20060307, 06:15 #15
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: INDEXMATCH (EXCEL 2003)
Many thanks HansV.
TIA
dubdub