Combined Excel formulas

[Shabbo]

Dear Sir,

I have an Excel sheet where I want to remove the helper column and just keep index match with combining Helper column in the same column D.

 

[vletm]

There are two sheets ...
with Sheet1 cell D2 modify C2 to A2&B2 and copy down
with Sheet2 cell D2 modify Sheet2!C2 to A2&B2 and copy down

 

[p45cal]

One thing to be aware of when concatenating values to use in VLOOKUP or MATCH is a possible incorrect match. The following 2 rows are different, yet the helper column is the same:



To help reduce the possibilities of such incorrect matches you can put an unusual character between the 2 values when you concatenate them, so instead of:
A2&B2
use say:
A2 & "¬" & B2

so you get this instead:

where you can see the helper column is different.
Of course, then you need to apply this to both the value you're looking up and the range of values you're looking in.

So
=INDEX(Sheet1!B:B,MATCH(A2 & "¬" & B2,Sheet1!A:A &"¬" & Sheet1!B:B,0))
In the above I'm returning the AMT column.
ps.
I've done something bad with the formula above; I've referred to entire columns which is resource-hungry, should really be more:
=INDEX(Sheet1!B1:B100,MATCH(A2 & "¬" & B2,Sheet1!A1:A100 &"¬" & Sheet1!B1:B100,0))