Using Index/Match or Vlookup to get one result out of multiple results

[mkadam]

Hi there. My very first post here. I'm an intermediate Excel user and have been working on a project.

I have a problem where I am using Index and Match formula to index one result (Using Match formula to determine both the row number and the column number in the Index formula).

The problem is that my data array has several results in the same column and I want to the output to be one particular result only. There is only one relevant result and the other results are something called 'null' in the same column. So I was hoping to use some formula like <>"null" as a condition but its just to working. I have been researching for a couple of days on this and used both Vlookup and Index/Match.

Is there any suggestions you could give me?

Right, now, by default my formula gives me the first result in the column. Here is my formulas (both Vlookup and Index/Match ones)
=VLOOKUP(B8,'Attendance tracker Jun 20'!C10:CE500,MATCH(D7,'Attendance tracker Jun 20'!J10:CE10,0))
=INDEX('Attendance tracker Jun 20'!$A$10:$CE$500,MATCH($B12,'Attendance tracker Jun 20'!$C$10:$C$500,0),MATCH(D$7,'Attendance tracker Jun 20'!$A$10:$CE$10,0))

 

[Jasesair]

I'm no expert, but have you had a fiddle with the exact match (0) at the end of each match function? Possibly a bit of trial and error there to get the desired result. I'm feeling your pain though, hearing you've spend a couple of days trying to find a solution!

 

[CA_Punit]

Is the relevant result a number or a text (Other than Null)

 

[offthelip]

You can do it very easily with a bit of VBa like this

Sub test()
'=VLOOKUP(B8,'Attendance tracker Jun 20'!C10:CE500,MATCH(D7,'Attendance tracker Jun 20'!J10:CE10,0))
datar = Worksheets("Attendance tracker Jun 20").Range("C10:CE500")
rowv = Range("B8:B8")
Colv = Range("D7:D7")
'find column
 colno = 0
 For i = 10 To UBound(datar, 2)
   If Colv = datar(10, i) Then
    colno = i
    Exit For
   End If
 Next i
 If colno > 0 Then
  ' look for the row with no null
   For j = 10 To UBound(datar, 1)
    If rowv = datar(j, 1) And datar(j, colno) <> "" Then
     res = datar(j, colno)
     Exit For
    End If
   Next j
 End If
 MsgBox res
 
 
End Sub

You could change this into a UDF
:

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this formula

=INDEX(INDEX('Attendance tracker Jun 20'!$A$10:$CE$500,0,MATCH(D$7,'Attendance tracker Jun 20'!$A$10:$CE$10,0)),AGGREGATE(15,6,(ROW('Attendance tracker Jun 20'!$C$10:$C$500)-ROW('Attendance tracker Jun 20'!$C$10)+1)/((INDEX('Attendance tracker Jun 20'!$A$10:$CE$500,0,MATCH(D$7,'Attendance tracker Jun 20'!$A$10:$CE$10,0))<>"null")*('Attendance tracker Jun 20'!$C$10:$C$500=$B12)),1))

 

[mkadam]

Thanks very much guys! All very helpful! I figured it out with your help!!

 

[Peter_SSs]

I figured it out with your help!!

Glad you got a successful outcome.

Since other readers may also be interested, could you post the result you ended up with?