Vlookup - Combine two columns

pto160

Active Member
Joined
Feb 1, 2009
Messages
473
Office Version
  1. 365
Platform
  1. Windows
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

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>A</td> <td>B</td> <td style="text-align: right;">10</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>B</td> <td>D</td> <td style="text-align: right;">20</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>R</td> <td>T</td> <td style="text-align: right;">50</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td> </td> <td> </td> <td>Result</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Lookup</td> <td>BD</td> <td style="text-align: right;">20</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Have you considered using an INDEX/MATCH array formula?

Sheet1

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>A </td><td>B </td><td style="text-align: right;">10</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>B </td><td>D </td><td style="text-align: right;">20</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>R </td><td>T </td><td style="text-align: right;">50</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td> </td><td> </td><td> </td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td> </td><td> </td><td>Result</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>Lookup </td><td>BD</td><td style="text-align: right;">20</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C6</td><td>{=INDEX(C1:C3,MATCH(B6,SUBSTITUTE(A1:A3&B1:B3," ",""),0))}</td></tr></tbody></table></td></tr></tbody></table>

Confirm it with Ctrl-Shift-Enter.
 
Upvote 0
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.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>A</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>B</TD><TD class=xl65 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>B</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>D</TD><TD class=xl65 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>R</TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>T</TD><TD class=xl65 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20></TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD><TD class=xl66 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20></TD><TD class=xl63 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64></TD><TD class=xl68 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #002060" width=64>Result</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #002060" width=64 height=21>Lookup</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: white" width=64>BD</TD><TD class=xl67 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ccffcc" width=64>20





</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
=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))
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top