1

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:

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
ruslanj
  • 11
  • 1

2 Answers2

2

You could try:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
1

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.

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • That is right, except for the fact OP mentioned *without concatenation* ;). Concatenation may sometimes be a bit tricky and cause false positives. In the case of OP I don't think it would. – JvdV Nov 05 '21 at 08:41
  • Hey @JvdV - that's what I thought as well. It looked to me that the issue was to replace VLOOKUP with INDEX MATCH and keep the concatenation. By the way, I still do not have LAMBDA, BYCOL, etc. Really frustrating. It may be my o365 account. – mark fitzpatrick Nov 05 '21 at 11:22
  • For me in was an option to just join the beta, then update office and boom, they are there. – JvdV Nov 05 '21 at 11:27
  • Ahh OK @JvdV - I have two accounts, but primarily use my corp account, which may not be allowed to use Early Adopters. But now I understand how you have it. – mark fitzpatrick Nov 05 '21 at 12:05