Copying VLOOKUP formula with non-contiguous entries across multiple workbooks

ajays314

New Member
Joined
Feb 5, 2018
Messages
2
I'll try to explain this as concisely as possible.

I have been given the task of cross-referencing several workbooks and creating a new, more easily-understandable excel document to be distributed internally within the company. It occurred to me that it would go much faster if I could apply a formula to check certain elements against each other and draw out the necessary data from the columns in that manner. To that end, I used VLOOKUP to successfully take the element I wanted from one workbook and insert it into the correct column in the new workbook.

However, because the data I'm cross-referencing in the old workbook is in non-contiguous rows (e.g. H843, H845, H934, etc.) and the rows in the new workbook are contiguous (H5, H6, H7, ...), trying to drag the formula down across the entries in the new workbook doesn't change it at all (at least, I assume it's to do with the non-contiguous/contiguous entries, perhaps it just doesn't work like that across workbooks).

Is there any way to remedy this, or can anyone offer a better solution to this task?

Thanks muchly,
Ajay
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

PatOBrien198

Board Regular
Joined
Sep 13, 2017
Messages
236
What is the formula that you are using?

Do you have the search arrays locked? (eg. $H$1:$H$12000 rather than: H1:H12000)
 

ajays314

New Member
Joined
Feb 5, 2018
Messages
2
What is the formula that you are using?

Do you have the search arrays locked? (eg. $H$1:$H$12000 rather than: H1:H12000)

The formula is as follows (names changed to defaults so as not to break company policy):
=VLOOKUP('[Workbook1.xlsx]Sheet1'!$H$853,'[Workbook1.xlsx]Sheet1'!$H$853:$Q$853,10,FALSE)
 

PatOBrien198

Board Regular
Joined
Sep 13, 2017
Messages
236
The formula is as follows (names changed to defaults so as not to break company policy):
=VLOOKUP('[Workbook1.xlsx]Sheet1'!$H$853,'[Workbook1.xlsx]Sheet1'!$H$853:$Q$853,10,FALSE)

Did you mean?
=VLOOKUP('[Workbook1.xlsx]Sheet1'!$H$853,'[Workbook2.xlsx]Sheet1'!$H$853:$Q$853,10,FALSE)

If so (you're looking in WB2) then you're looking at only one row? 853?

I would have expected something like $H$10:$Q$12000 - so that the vlookup is searching through a bunch of rows, not just one.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,748
Members
418,149
Latest member
amamiche67

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
Top