Retrieve 2 columns of data to the left of lookup value on another sheet.

rabbitsfeat

New Member
Joined
Jun 18, 2017
Messages
20
I have 2 workbooks, the first one has empty columns and I want to pull that data from another workbook. They look like this:

A
B
C
bob@comic.com
geoff@live.co.uk
bobby@live.co.uk
bill@hotmail.com
wes@sky.com

<tbody>
</tbody>









A
B
C
D
E
F
6/18/2017
111-2222222-3333333
1179714
JA5939
AMZ
bob@comic.com
6/18/2017
222-3333333-4444444
1179723
NE1347
AMZ
geoff@live.co.uk
6/18/2017
333-4444444-5555555
1179727
JE1133
AMZ
bobby@live.co.uk
6/18/2017
444-5555555-6666666
1179722
DA5721
AMZ
bill@hotmail.com
6/18/2017
555-6666666-7777777
1179745
SA2294
AMZ
wes@sky.com

<tbody>
</tbody>











What I need is a function that takes the email address in column A of sheet1, finds it in sheet 2 (which is a separate workbook), then finds the data in columns C and D for that email address, and pulls it back into sheet one, into colums C and D.

Because the data I want is to the left of the email address in sheet 2, I can't figure out how to pull the data from columns C and D at the same time. I can do them one at a time but not both together... is this possible?

Here's what I have so far:

{=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}

That pulls the data from column C, then this for column D:

{=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}

I've used INDEX and MATCH because as far as I can tell, VLOOKUP can't look left.

I also tried looking for a function that reverses the order of the columns in the second workbook, but to no avail.

If anyone can help me with this I'd be extremely grateful.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
do you want the results of C & D in the one cell?
index match is the function you want. It is the vlookup in either direction.
If you want C & D in the same cell you can consolidate the values
{=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))&" "&INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}
That might be what you're looking for
 
Upvote 0
do you want the results of C & D in the one cell?
index match is the function you want. It is the vlookup in either direction.
If you want C & D in the same cell you can consolidate the values
{=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))&" "&INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}
That might be what you're looking for

Hi, firstly thank you for your help.

I actually want the results of C & D in two cells though, and over two columns. Columns B & C.

Hope you can help with this.
 
Upvote 0
Try this,
B1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
C1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)
 
Last edited:
Upvote 0
Try this,
B1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
C1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)

I will try this. Can it be put in the formula bar all at once and applied to the 2 cells, or does it have to be done 1 column at a time.

To confuse matters further. Ignoring the above tables for a second. I actually want to pull data from columns C+D from the external workbook, and put into columns D+E on the other sheet. If this is possible how would this code need to change?

Thank you in advance for any help.
 
Upvote 0
Hi,
It can be put in the formula bar. It needs to be put into the cell for each result. it can be copied down. The numbers in the red text indicate the column number. eg 3 = column C.

in cell D1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
in Cell E1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)

Hope this helps
 
Upvote 0
Hi,
It can be put in the formula bar. It needs to be put into the cell for each result. it can be copied down. The numbers in the red text indicate the column number. eg 3 = column C.

in cell D1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
in Cell E1
=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)

Hope this helps

What am I doing wrong because this still doesn't work. It just copies the formula into the cell.

=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$2:$F$2207,MATCH(C3,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$2:$F$2207,0),3)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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