Vlookup on multiple columns not working

[robcampbell]

This Vlookup should work I think but it isn't.

Current table does have more columns in between Service Indicator and Support Activity. There is a column at the end of the current table to hold the formula

Formula
{=INDEX(Table5[@[MSR Category ]],MATCH(Table5[Service Indicator]&Table5[Support Activity],[@[Service Indicator]]&[@[Support Activity]],0))}

Table5

[TABLE="width: 552"]
<tbody>[TR]
[TD]Service Indicator[/TD]
[TD]Support Activity[/TD]
[TD]MSR Category[/TD]
[/TR]
[TR]
[TD]0% error[/TD]
[TD]Update gradebook[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]21 day auto-drop[/TD]
[TD]Provide policy instruction[/TD]
[TD]Admin[/TD]
[/TR]
[TR]
[TD]Accidental drop[/TD]
[TD]Support enrollment[/TD]
[TD]Admin[/TD]
[/TR]
[TR]
[TD]Account merge needed[/TD]
[TD]Merge account[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]All Attempts Used[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]Answer changed[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]Attempts out of order[/TD]
[TD]Update gradebook[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]Auto submit[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]Blank gray screen[/TD]
[TD]Troubleshoot[/TD]
[TD]System[/TD]
[/TR]
[TR]
[TD]Broken link - CL[/TD]
[TD]Update course content[/TD]
[TD]Content[/TD]
[/TR]
[TR]
[TD]Broken link - DL[/TD]
[TD]Update course content[/TD]
[TD]Content[/TD]
[/TR]
[TR]
[TD]CL content assistance[/TD]
[TD]Escalate request[/TD]
[TD]Admin[/TD]
[/TR]
[TR]
[TD]Content challenge[/TD]
[TD]Reset exam[/TD]
[TD]Content[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Result Table


[TABLE="width: 573"]
<tbody>[TR]
[TD]Service Indicator[/TD]
[TD]Support Activity[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]0% error[/TD]
[TD]Update gradebook[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]21 day auto-drop[/TD]
[TD]Provide policy instruction[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Accidental drop[/TD]
[TD]Support enrollment[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Account merge needed[/TD]
[TD]Merge account[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]All Attempts Used[/TD]
[TD]Reset exam[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Answer changed[/TD]
[TD]Reset exam[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Attempts out of order[/TD]
[TD]Update gradebook[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Auto submit[/TD]
[TD]Reset exam[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Blank gray screen[/TD]
[TD]Troubleshoot[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Broken link - CL[/TD]
[TD]Update course content[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Broken link - DL[/TD]
[TD]Update course content[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]CL content assistance[/TD]
[TD]Escalate request[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]Content challenge[/TD]
[TD]Reset exam[/TD]
[TD]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

 

[robcampbell]

Even when the result table isn't the same? What if the first row in the result table is row 6 and there are duplicates of that? The result table is a report. The report has 266 rows. The report is compared to table5 to get the proper MSR Category.

This is what I'm getting. Maybe I have some other settings wrong? Calculation Options are set to Automatic.

{=INDEX(Table3[@MSR],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(Table3[@[Service Indicator]]&Table3[@[Support Activity]],0),0))}

[TABLE="class: grid, width: 1043, align: left"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Service Indicator[/TD]
[TD]Support Activity[/TD]
[TD]MSR[/TD]
[TD][/TD]
[TD][/TD]
[TD]Service Indicator[/TD]
[TD]Support Activity[/TD]
[TD]MSR[/TD]
[/TR]
[TR]
[TD]0% error[/TD]
[TD]Update gradebook[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]0% error[/TD]
[TD]Update gradebook[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]21 day auto-drop[/TD]
[TD]Provide policy instruction[/TD]
[TD]Admin[/TD]
[TD][/TD]
[TD][/TD]
[TD]21 day auto-drop[/TD]
[TD]Provide policy instruction[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Accidental drop[/TD]
[TD]Support enrollment[/TD]
[TD]Admin[/TD]
[TD][/TD]
[TD][/TD]
[TD]Accidental drop[/TD]
[TD]Support enrollment[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Account merge needed[/TD]
[TD]Merge account[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]Account merge needed[/TD]
[TD]Merge account[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]All Attempts Used[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]All Attempts Used[/TD]
[TD]Reset exam[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Answer changed[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]Answer changed[/TD]
[TD]Reset exam[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Attempts out of order[/TD]
[TD]Update gradebook[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]Attempts out of order[/TD]
[TD]Update gradebook[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Auto submit[/TD]
[TD]Reset exam[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]Auto submit[/TD]
[TD]Reset exam[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Blank gray screen[/TD]
[TD]Troubleshoot[/TD]
[TD]System[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blank gray screen[/TD]
[TD]Troubleshoot[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Broken link - CL[/TD]
[TD]Update course content[/TD]
[TD]Content[/TD]
[TD][/TD]
[TD][/TD]
[TD]Broken link - CL[/TD]
[TD]Update course content[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Broken link - DL[/TD]
[TD]Update course content[/TD]
[TD]Content[/TD]
[TD][/TD]
[TD][/TD]
[TD]Broken link - DL[/TD]
[TD]Update course content[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]CL content assistance[/TD]
[TD]Escalate request[/TD]
[TD]Admin[/TD]
[TD][/TD]
[TD][/TD]
[TD]CL content assistance[/TD]
[TD]Escalate request[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Content challenge[/TD]
[TD]Reset exam[/TD]
[TD]Content[/TD]
[TD][/TD]
[TD][/TD]
[TD]Content challenge[/TD]
[TD]Reset exam[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD]Content error[/TD]
[TD]Review of 508 compliance[/TD]
[TD]508[/TD]
[TD][/TD]
[TD][/TD]
[TD]Content error[/TD]
[TD]Review of 508 compliance[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]





























Evaluation:
INDEX($C$2,MATCH("0% errorUpdate gradebook",INDEX("0% errorUpdate gradebook",0),0))
INDEX($C$2,MATCH("0% errorUpdate gradebook",#VALUE!,0))
INDEX($C$2,#VALUE!)
#VALUE!

 

[Marcelo Branco]

Even when the result table isn't the same? What if the first row in the result table is row 6 and there are duplicates of that? The result table is a report. The report has 266 rows. The report is compared to table5 to get the proper MSR Category.

This is what I'm getting. Maybe I have some other settings wrong? Calculation Options are set to Automatic.

{=INDEX(Table3[@MSR],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(Table3[@[Service Indicator]]&Table3[@[Support Activity]],0),0))}

Are you searching Table3 or Table5?

M.

 

[robcampbell]

Now it's table3. I moved both to a to a single sheet to try to get the results you have.

 

[Marcelo Branco]

Now it's table3. I moved both to a to a single sheet to try to get the results you have.

What are the names of the tables?
Data table - from which you want to get (pull) the results: Name?
Result table - where do you want to enter the formula: Name?

M.

 

[robcampbell]

Right now (and going forward) the Data table is MSR.
Right now the result table is table5 in the test workbook sheet.
Right now the result table of the actual report is table1.

The MSR table is the key. Any report/data dump that I want to compare to it shouldn't matter, right? If I add a working formula to the result table, it will look at the data table (MSR) and populate accordingly, right?

 

[Marcelo Branco]

Ok. Let's use the test workbook with two tables
Data in table: MSR
Result table: Table5

So try in Table5 this formula
=INDEX(MSR[MSR Category],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR[Service Indicator]&MSR[Support Activity],0),0))

M.

 

[robcampbell]

This works when it's in the same workbook. When I tried =INDEX(MSR.xlsx!MSR[@[MSR Category]],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR.xlsx!MSR[@[Service Indicator]]&MSR.xlsx!MSR[@[Support Activity]],0),0)) in a different workbook, it did not work.
When evaluating I see
INDEX([MSR.xlsx]MSR!$C$2,MATCH("0% errorUpdate gradebook",INDEX("0% errorUpdate gradebook",0),0))
INDEX([MSR.xlsx]MSR!$C$2,MATCH("0% errorUpdate gradebook",#VALUE!,0))

 

[Marcelo Branco]

This works when it's in the same workbook. When I tried =INDEX(MSR.xlsx!MSR[@[MSR Category]],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR.xlsx!MSR[@[Service Indicator]]&MSR.xlsx!MSR[@[Support Activity]],0),0)) in a different workbook, it did not work.
When evaluating I see
INDEX([MSR.xlsx]MSR!$C$2,MATCH("0% errorUpdate gradebook",INDEX("0% errorUpdate gradebook",0),0))
INDEX([MSR.xlsx]MSR!$C$2,MATCH("0% errorUpdate gradebook",#VALUE!,0))

Great that works with both tables in the same workbook! I have not tried with two different workbooks - why are you trying with two workbooks?

Anyway, this is not right (in red)
=INDEX(MSR.xlsx!MSR[@[MSR Category]],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR.xlsx!MSR[@[Service Indicator]]&MSR.xlsx!MSR[@[Support Activity]],0),0))

See if this works
=INDEX(MSR.xlsx!MSR[MSR Category],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR.xlsx!MSR[Service Indicator]&MSR.xlsx!MSR[Support Activity],0),0)).

M.

 

[Marcelo Branco]

With the two workbooks opened this worked for me
=INDEX(MSR.xlsx!MSR[Category],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(MSR.xlsx!MSR[Service Indicator]&MSR.xlsx!MSR[Support Activity],0),0))

M.

 

[robcampbell]

That was it. The @ was messing things up. That @ was put there when I selected the field. I didn't type that in there myself.

Thank you so much!

Do you know what the '@' is supposed to represent?