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.
 
check in your formula tab that "show formula is not selected"

No that doesn't fix it. Having put the formula in the formula bar, I'm hitting Ctrl+Shift+Enter. I just get the formula in the cell D2.

It may not be important but the brackets surrounding this part of the formula are showing in red: (C3,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$2207,0)
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,
yes you do need the [], I must apologies as I left out the '. give a shot now.
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)
 
Upvote 0
Hi,
yes you do need the [], I must apologies as I left out the '. give a shot now.
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)

Still not working but getting there. What I get now is a dialog box opening saying: Update values: EbayAmazonWeeklyRepot. When I click Ok I get #N/A in all the cells.
 
Upvote 0
Still not working but getting there. What I get now is a dialog box opening saying: Update values: EbayAmazonWeeklyRepot. When I click Ok I get #N/A in all the cells.

Okay I've got it to work with this:

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

I'm now trying to add an IFERROR function so that when there's no email address that matches, it just leaves the cell blank, so I don't have to go and delete all the occurances of #N/A.

I can't seem to get this to work though.

Again any help would be much appreciated. Thank you.
 
Upvote 0
=INDEX([EbayAmazonWeeklyReport.xlsx]Sheet1!$A$2:$F$2166,MATCH(C3,[EbayAmazonWeeklyReport.xlsx]Sheet1!$F$2:$F$2166,0),3)

It's not working again. How could it work yesterday but now it's not today??
 
Upvote 0
Sorry been away for a while. I don't understand how it is working one day then it is not. it may be that you are loosing the links of the file. Maybe moved or renamed.
to add the if error,
Code:
[COLOR=#333333]=IFERROR(INDEX([EbayAmazonWeeklyReport.xlsx]Sheet1!$A$2:$F$2166,MATCH(C3,[EbayAmazonWeeklyReport.xlsx]Sheet1!$F$2:$F$2166,0),3),"")[/COLOR]
 
Upvote 0
I've sorted it now. It was because the formatting was set to text. I changed it to general and all works again.

One more question... having run this function, I'm left with quite a few cells that are empty, which is what I wanted. They do however still have formatting applied to them, and I need to type information into these cells manually. So I don't want any formatting on them.

At the moment I'm using this method to remove the formula from each cell 1 at a time (which is quite time consuming)... select the cell, click find and select - go to - special - current array - ok, then hit the delete key.

Is there an easier and quicker way to do this? Can I do it highlighting all the cells I don't want the formula applied to?

Thanks again for all your help everyone.
 
Upvote 0
There are 2 ways to do the filter. Ensure you have selected your headings
1. goto the data tab and select the filters.
2 shortcut SHIFT+CTL+L
you will see there is a drop down arrow. you can select this. remove the first tick, then tick the blanks.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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