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
 

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.
 

Forum statistics

Threads
1,081,661
Messages
5,360,338
Members
400,581
Latest member
Eskimo

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top