Curious Question ABout VLOOKUP and Concatenate the Table Array

[shyy]

Hey guys,

I was curious, is it possible to concatenate the table array. For example the lookup value would be concatenated as well. Recently someone posted a thread and I mentioned to use SUM or SUMPRODUCT since they want to return numbers. It got me thinking, would it be possible to do a VLOOKUP instead. I understand SUMPRODUCT can do that.

This would look something like:

=VLOOKUP(A11&B11,A2:A6&B2:B6,2,FALSE)

Evaluating the lookup value shows "123Europe"
Evaluating the table array shows {"123US";"456Asia";"123Europe";"333Europe";"456US"}

Thanks in advance

Excel 2010

	A	B	C
Order	Region
US
Asia
Europe
Europe
US
Order	Region
Europe
Europe

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]555[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]987[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]556[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]333[/TD]

[TD="align: right"]789[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]456[/TD]

[TD="align: right"]988[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]556[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]333[/TD]

[TD="align: right"]789[/TD]

</tbody>

Sheet12​

 

[circledchicken]

Hi,

I'm not sure about using the VLOOKUP function in that way, but here are some alternative examples (using CTRL-SHIFT-ENTER rather than just ENTER as these are all array formulae):

INDEX / MATCH:

=INDEX(
    C$2:C$6,
    MATCH(A11 & B11, A$2:A$6 & B$2:B$6, 0))

INDEX / MATCH / IF

=INDEX(
    C$2:C$6,
    MATCH(
      1,
      IF(A$2:A$6 = A11,
      IF(B$2:B$6 = B11,
        1)),
      0))

SUM/IF (although it is probably a better idea to use SUMIFS or SUMPRODUCT if your trying to SUM the values):

=SUM(
    IF(A$2:A$6 = A11,
    IF(B$2:B$6 = B11,
      C$2:C$6)))

 

[Andrew Poulsom]

You can use:

=INDEX(C$2:C$6,MATCH(1,INDEX((A$2:A$6=A11)*(B$2:B$6=B11),),FALSE))

 

[shyy]

Thank You guys, I should have been more specific. I know how to do this in different ways, just wondering if it was possible to do it via VLOOKUP in array or non array.

 

[shyy]

another Question, what does the 1 do with the MATCH function? I see it being used for other functions as well.

 

[Andrew Poulsom]

another Question, what does the 1 do with the MATCH function? I see it being used for other functions as well.

Multiplication of booleans returns 1 or 0. MATCH finds the first 1 (both conditions TRUE).

 

[shyy]

Thanks for the info

Multiplication of booleans returns 1 or 0. MATCH finds the first 1 (both conditions TRUE).