# Vlookup - Combine two columns

This is a discussion on Vlookup - Combine two columns within the Excel Questions forums, part of the Question Forums category; I am trying to do a vlookup by combining two columns in the array to match the lookup value. I ...

1. ## Vlookup - Combine two columns

I am trying to do a vlookup by combining two columns in the array to match the lookup value. I know how to this with Index,Match.
Can this be done with vlookup without using a helper column?
Sheet1

 A B C 1 A B 10 2 B D 20 3 R T 50 4 5 Result 6 Lookup BD 20

Excel tables to the web >> Excel Jeanie HTML 4

2. ## Re: Vlookup - Combine two columns

Have you considered using an INDEX/MATCH array formula?

Sheet1

 A B C 1 A B 10 2 B D 20 3 R T 50 4 5 Result 6 Lookup BD 20

 Cell Formula C6 {=INDEX(C1:C3,MATCH(B6,SUBSTITUTE(A1:A3&B1:B3," ",""),0))}

Confirm it with Ctrl-Shift-Enter.

3. ## Re: Vlookup - Combine two columns

Maybe:

Formula in cell C6:

=INDEX(C1:C3,INDEX(MATCH(B6,A1:A3&B1:B3,0),))

(Does not require Ctrl + Shift + Enter)

or

=INDEX(C1:C3,MATCH(B6,A1:A3&B1:B3,0))

Requires Ctrl + Shift + Enter.

 A B 10 B D 20 R T 50 Result Lookup BD 20

4. ## Re: Vlookup - Combine two columns

Hey delaneyjm,

Why are you using the SUBSTITUTE to find a space and replace it with a blank? When I evaluate A1:A3&B1:B3, I see no spaces. Is this a technique that you use just in case the data has spaces - like a safety move?

5. ## Re: Vlookup - Combine two columns

Thanks. So using Index match is my best option.
So there is no easy way to use vlookup. What I like about vlookup is that I can change the column number very easy if the lookup is on the far left.

6. ## Re: Vlookup - Combine two columns

Originally Posted by pto160
Thanks. So using Index match is my best option.
So there is no easy way to use vlookup. What I like about vlookup is that I can change the column number very easy if the lookup is on the far left.
If you insist on VLOOKUP...

Control+shift+enter, not just enter:

=VLOOKUP(B6,CHOOSE({1,2},\$A\$1:\$A\$3&\$B\$1:\$B\$3,\$C\$1:\$C\$3),2,0)

7. ## Re: Vlookup - Combine two columns

Hey Mike,

Seems when I pasted the Excel Jeanie output from the OP, it included some trailing spaces that I didn't pick up on.

8. ## Re: Vlookup - Combine two columns

Originally Posted by mgirvin
=INDEX(C1:C3,INDEX(MATCH(B6,A1:A3&B1:B3,0),))

(Does not require Ctrl + Shift + Enter)
There can be such impression that any permutations of INDEX & MATCH functions are working :
=INDEX(C1:C3,MATCH(B6,INDEX(A1:A3&B1:B3,),0))

9. ## Re: Vlookup - Combine two columns

A question for Aladin.

that is the second time I have seen you or someone use CHOOSE {1,2}
in a formula, what does it do please?

10. ## Re: Vlookup - Combine two columns

Originally Posted by Dryver14
A question for Aladin.

that is the second time I have seen you or someone use CHOOSE {1,2}
in a formula, what does it do please?
CHOOSE collects (the results of) its arguments into an array if the index (below: {1,2}) is an array constant as in:

CHOOSE({1,2},"a","b")

==> {"a","b"}

However, if the index is a scalar, it will return (the result of) the positionally corresponding argument as in:

CHOOSE(2,"a","b")

==> b

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•