VLOOKUP where LOOKUP range is two columns

J

Jon Jagd

Board Regular
Joined
Jul 31, 2002
Messages
72
Hi

I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.

My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:

=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)

But that obviously does not work. Any suggetions on how to slve this?

Jon
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Jon Jagd said:
Hi

I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.

My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:

=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)

But that obviously does not work. Any suggetions on how to slve this?

Jon
Click to expand...
Do you mean...

=VLOOKUP(A2&B2;'[OtherWorkbook.xlsx]Sheet1'!$M$2:$N$9;2;0)

which compares A2&B2 with M2:M9?
 
Upvote 0
Hi I would suggest you to concatenate the Source columns in difference sheet & use vlookup formula

The ranges cannot be concatenated.
 
Upvote 0
amir_urmila said:
Hi I would suggest you to concatenate the Source columns in difference sheet & use vlookup formula

The ranges cannot be concatenated.
Click to expand...

They can. Try for example...

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 144pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=192 colSpan=3>Table</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>fad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>fad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>fad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>x</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>kad</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

H2, control+shift+enter, not just enter:

=INDEX(C2:C4,MATCH(F2&G2,A2:A4&B2:B4,0))

However, it's not clear that the OP needs such.
 
Upvote 0
The INDEX and MATCH combination did the job beautifully! Thanks a lot for the input.

Br,
Jon
 
Upvote 0
Thank you so much for this example!

What if you had this table instead (i.e. the x applied to everything below it until the y appears in the 4th row) and wanted to find the 2 insted of the 3.
Essentially, I want to use your formula and eliminate the step of copying and pasting the x into the 2nd and 3rd row.

Thanks in advance for your assistance!

Table
x
fad
3
x
gad
2
gad
2
kad
6
y
fad

<TBODY>
</TBODY>


Aladin Akyurek said:
They can. Try for example...

Table
x
fad
3
x
fad
3
y
fad
2
x
kad
6

<TBODY>
</TBODY>


H2, control+shift+enter, not just enter:

=INDEX(C2:C4,MATCH(F2&G2,A2:A4&B2:B4,0))

However, it's not clear that the OP needs such.
Click to expand...
 
Upvote 0
kk111 said:
Thank you so much for this example!

What if you had this table instead (i.e. the x applied to everything below it until the y appears in the 4th row) and wanted to find the 2 insted of the 3.
Essentially, I want to use your formula and eliminate the step of copying and pasting the x into the 2nd and 3rd row.

Thanks in advance for your assistance!

Table
x
fad
3
x
gad
2
gad
2
kad
6
y
fad

<TBODY>
</TBODY>
Click to expand...

If we accept the cost of having a lay-out as specified...

H2, control+shift+enter, not just enter:
Rich (BB code): 
=VLOOKUP(G2,OFFSET($B$2,MATCH(F2,$A$2:$A$5,0)-1,0,
   LOOKUP(9.99999999999999E+307,CHOOSE({1,2},ROWS($A$2:$A$5),
   MATCH(1,IF(1-(A2:A5=""),IF(1-(A2:A5=F2),
   IF(ROW(A2:A5)-ROW(A2)+1>MATCH(F2,$A$2:$A$5,0),1))),0)))-
   MATCH(F2,$A$2:$A$5,0)+1,2),2,0)

If we reserve a cell for the repetitive part in this formula...

I2, just enter:
Rich (BB code): 
=MATCH(F2,$A$2:$A$5,0)

H2, still confirmed with control+shift+enter, would become:
Rich (BB code): 
=VLOOKUP(G2,OFFSET($B$2,I2-1,0,
  LOOKUP(9.99999999999999E+307,CHOOSE({1,2},ROWS($A$2:$A$5),
  MATCH(1,IF(1-(A2:A5=""),IF(1-(A2:A5=F2),
  IF(ROW(A2:A5)-ROW(A2)+1>I2,1))),0)))-I2+1,2),2,0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
vLookup Generation
Replies
3
Views
303
StephenCrump
S
A
  • Question
hmmm, vlookup with an if statement as a worksheet function in VBA....
Replies
1
Views
93
ajm
A
S
  • Solved
Multiple embedded IF statements with VLOOKUPs not working
Replies
2
Views
189
shawnrpg
S
J
  • Solved
Vlookup and Concatenate
Replies
5
Views
134
Fluff
Fluff
D
  • Question
VLookup returns the result from the row above
Replies
8
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,206,971
Messages
6,075,926
Members
446,171
Latest member
Maddogg4Life

Share this page

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