VLOOKUP only matching the first row and will not copy/paste

eaveni4287

New Member
Joined
Aug 20, 2020
Messages
4
Platform
  1. MacOS
VLOOKUP will only match the first row value and then continues to replicate that value when I copy/paste it down the entire column. It won't change the formula from $A$1 to $A$2, etc. It just stays at $A$1.

It is set to Automatic so it is NOT that issue. I have also tried removing the $ in front of each of the A and the 1 and then it stops matching correctly all together. It is performing this error for every single sheet I try. New sheets, old sheets, any sheets. It is like the function has stopped working correctly all together. I have spent hours trying to figure out what is going on and I am all out of options. Someone please help!

Here is the formula:
=VLOOKUP($A$1,[Book6]Sheet1!$1:$1048576,2,FALSE)

Screenshot attached for reference.

I am on a Mac if that helps. Not sure what version of Excel I'm using.
 

Attachments

  • Screen Shot 2020-08-20 at 3.22.26 PM.png
    Screen Shot 2020-08-20 at 3.22.26 PM.png
    70.4 KB · Views: 120

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You have used Absolute Reference

You require relative reference
Just Select A1
 
Upvote 0
As far as I can see, what you want should be

=VLOOKUP($A1,[Book6]Sheet1!$A$1:$B$1000,2,FALSE)

or you could use

=VLOOKUP($A1,[Book6]Sheet1!$A:$B,2,FALSE)

but it is better to limit the second part to a realistic number of rows as I have done with the first example, just change 1000 to whatever is needed (1000 is ideal if you will never have more than a few hundred rows of data in book6).

You can set it up so that it finds the last row automatically (dynamic range) but that may be a bit too much for you to try and set up yet.
 
Upvote 0
Book1.xlsx
ABCDEF
1
2
3ORCALE1MOAT4
4ODC2GRAPESHOT3
5GRAPESHOT3ODC2
6MOAT4ORCALE1
7
Sheet3
Cell Formulas
RangeFormula
F3:F6F3=VLOOKUP(E3,A:B,2,FALSE)
 
Upvote 0
You have used Absolute Reference

You require relative reference
Just Select A1
When I just use A1 it works for this test sheet, however when I go to use it on the actual document I'm working on it doesn't perform the match correctly but rather just copy/pastes all the values directly onto the sheet instead of matching them to what they should correspond to. I tried this solution already as well.
 
Upvote 0
If it works on your test sheet but not your real one then there is a significant difference between the 2 which is causing the problem.
We can only suggest solutions based on what we can see.
 
Upvote 0
If it works on your test sheet but not your real one then there is a significant difference between the 2 which is causing the problem.
We can only suggest solutions based on what we can see.

Here is a screenshot of the actual data sheet I am using. When I change the values from $A$1 to A1 it does make it dynamic but it does not perform the match function correctly. As you can see, it just copy/pastes the values in Column B into the second workbook as opposed to actually matching them.

Formula: =VLOOKUP([Book2]Sheet1!A2,[Book2]Sheet1!$1:$1048576,2,FALSE)
 

Attachments

  • Screen Shot 2020-08-20 at 3.40.45 PM.png
    Screen Shot 2020-08-20 at 3.40.45 PM.png
    231.9 KB · Views: 73
Upvote 0
First part should be referring to book3, not book2, although you don't actually need the book or sheet name for a cell that is on the same sheet.

=VLOOKUP([Book2]Sheet1!A2,[Book2]Sheet1!$1:$1048576,2,FALSE)

=VLOOKUP(A2,[Book2]Sheet1!$A$1:$B$1000,2,FALSE)
 
Last edited:
Upvote 0
First part should be referring to book3, not book2, although you don't actually need the book or sheet name for a cell that is on the same sheet.

=VLOOKUP([Book2]Sheet1!A2,[Book2]Sheet1!$1:$1048576,2,FALSE)

=VLOOKUP(A2,[Book2]Sheet1!$A$1:$B$1000,2,FALSE)

YOU ARE MY SAVIOR! OMG I seriously wish I could buy you a beer. I have spent all afternoon on this dumb error.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
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