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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
What is the formula that you are using?

Do you have the search arrays locked? (eg. $H$1:$H$12000 rather than: H1:H12000)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

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