Excel - vlookup value - combination of2 columns (without concat) from another sheet-CodePudding

I want to write kind of formula (look-up) where i can match and bring data from another sheet using combination of 2 columns without concatenation. I am doing using following formula at the moment:

 VLOOKUP(A1&B1,'other_sheet'!A:D,4,0)

where Colomn A in other sheet in concat of B1 and C1 (B1&C1)

However, my intention is to to get same result without doing any concat as you can see in below pic:

CodePudding user response：

In your example, you can do:

=INDEX('other_sheet'!D:D, MATCH(A1&B1,'other_sheet'!A:A&'other_sheet'!B:B,0))

where 'other_sheet'!A:A, 'other_sheet'!B:B, and 'other_sheet'!D:D might also be a range like 'other_sheet'!A1:A100, 'other_sheet'!B1:B100 and 'other_sheet'!D1:D100.

CodePudding user response：

You could try:

For microsoft365:

=FILTER(G$2:G$9,(E$2:E$9=A2)*(F$2:F$9=B2),"")

Drag down. Or if you don't want to drag down the formula and BYROW() is available:

=BYROW(A2:B6,LAMBDA(a,FILTER(G2:G9,MMULT(--(E2:F9=a),{1,1})=2)))

For older versions of Excel try:

=INDEX(G$2:G$9,MATCH(1,INDEX((E$2:E$9=A2)*(F$2:F$9=B2),),0))

Or:

=LOOKUP(2,1/((E$2:E$9=A2)*(F$2:F$9=B2)),G$2:G$9)

Drag down.