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]

 

[Ron Coderre]

Not exactly sure what you want returned without expected results.
Perhaps this regular (non-array) formula will do what you want.

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

Is that something you can work with?

 

[robcampbell]

The expected result is that Result Table should look like Table 5. Since the values are the same, the outcome should be the same.

When I apply your formula, it sets the first value to System which is correct but when I apply it to the rest of the column, all of them are set to System.

 

[Ron Coderre]

I can't duplicate that experience, Rob.
Since you're using an Excel table, as soon as you put the formula in the top data row cell...Excel should automatically copy it down into the remaining table cells.

Actually....I can break it to get the erroneous results if I:
• Set Calculation to Manual
• Copy the formula into the top row cell
• Erase the autofilled formulas
• Copy the top formula down into the other cells.
However...as soon as I set Calculation to Automatic, all of the correct results display.

 

[Marcelo Branco]

@Ron

I think your formula should be
=INDEX(Table5[MSR Category],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(Table5[Service Indicator]&Table5[Support Activity],0),0))

M.

 

[Ron Coderre]

Marcelo.....
Hmmm.....I copied the formula right off my Excel 2013 worksheet.
Based on this: " There is a column at the end of the current table to hold the formula"
I assumed the formula was within a Table5 column. If that's not the case, then 'Yes' the table ref would be required.

 

[Marcelo Branco]

Marcelo.....
Hmmm.....I copied the formula right off my Excel 2013 worksheet.
Based on this: " There is a column at the end of the current table to hold the formula"
I assumed the formula was within a Table5 column. If that's not the case, then 'Yes' the table ref would be required.

Ok, i understand what you did.
I assumed that Result Table is a different table.
Let's wait what robcampbell says...

M.

 

[robcampbell]

I can't duplicate that experience, Rob.
Since you're using an Excel table, as soon as you put the formula in the top data row cell...Excel should automatically copy it down into the remaining table cells.

Actually....I can break it to get the erroneous results if I:
• Set Calculation to Manual
• Copy the formula into the top row cell
• Erase the autofilled formulas
• Copy the top formula down into the other cells.
However...as soon as I set Calculation to Automatic, all of the correct results display.

I did notice that for some reason the calculation was no longer automatic. Once I am back on my system I will set it back to automatic. I had surgery on 5/3 so I haven't been on my computer til today but the spreadsheet is on my windows machine so I'll update by tomorrow. Thanks for info.

 

[robcampbell]

I've finally had a chance to get back to this. I've tried both formulas but neither work. When evaluating I notice that it compares only one line with one line rather than one line with the entire table. However, even though the second line matches it doesn't return the value. They all say #VALUE !

My apologies for the late response but I recently had surgery and couldn't type. I had someone else type my last response for me but I haven't been able to work until now.

 

[Marcelo Branco]

The formula in post 5 worked perfectly for me

Table5 iin A1:C14;
Result Table in F1:H14


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Service Indicator​

[/TD]
[TD]

Support Activity​

[/TD]
[TD]

MSR Category​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Service Indicator​

[/TD]
[TD]

Support Activity​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

0% error​

[/TD]
[TD]

Update gradebook​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0% error​

[/TD]
[TD]

Update gradebook​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[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]

Admin​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Accidental drop​

[/TD]
[TD]

Support enrollment​

[/TD]
[TD]

Admin​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Accidental drop​

[/TD]
[TD]

Support enrollment​

[/TD]
[TD]

Admin​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[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]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[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]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Answer changed​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Answer changed​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[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]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Auto submit​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Auto submit​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Blank gray screen​

[/TD]
[TD]

Troubleshoot​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Blank gray screen​

[/TD]
[TD]

Troubleshoot​

[/TD]
[TD]

System​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[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]

Content​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[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]

Content​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[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]

Admin​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Content challenge​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

Content​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Content challenge​

[/TD]
[TD]

Reset exam​

[/TD]
[TD]

Content​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formula in H2 (see post 5)
=INDEX(Table5[MSR Category],MATCH([@[Service Indicator]]&[@[Support Activity]],INDEX(Table5[Service Indicator]&Table5[Support Activity],0),0))

M.