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

mkadam

New Member
Joined
Jul 3, 2020
Messages
2
Office Version
2019
Platform
Windows, MacOS
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))
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
96
Office Version
2016
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

Well-known Member
Joined
Nov 18, 2019
Messages
866
Office Version
365
Platform
Windows
Is the relevant result a number or a text (Other than Null)
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,397
Office Version
2010
Platform
Windows
You can do it very easily with a bit of VBa like this
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows

ADVERTISEMENT

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

New Member
Joined
Jul 3, 2020
Messages
2
Office Version
2019
Platform
Windows, MacOS
Thanks very much guys! All very helpful! I figured it out with your help!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,175
Messages
5,509,598
Members
408,743
Latest member
leen1234

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top