INDEX-MATCH (EXCEL 2003)

Topic

New Reply

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.

ID

TOP

BOT

REF

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

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) = 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

Change the formula in D1 to:

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

Steve

Reply | Quote

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?

Reply | Quote

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) = 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.

Reply | Quote

Many thanks HansV.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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)

	A	B	C	D	E	F	G	H
1	ID	TOP	BOT	REF

Reply | Quote

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

Reply | Quote