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: 6

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
You have used Absolute Reference

You require relative reference
Just Select A1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
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.
 

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Book1.xlsx
ABCDEF
1
2
3ORCALE1MOAT4
4ODC2GRAPESHOT3
5GRAPESHOT3ODC2
6MOAT4ORCALE1
7
Sheet3
Cell Formulas
RangeFormula
F3:F6F3=VLOOKUP(E3,A:B,2,FALSE)
 

eaveni4287

New Member
Joined
Aug 20, 2020
Messages
4
Platform
  1. MacOS

ADVERTISEMENT

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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
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.
 

eaveni4287

New Member
Joined
Aug 20, 2020
Messages
4
Platform
  1. MacOS

ADVERTISEMENT

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: 7

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,616
Office Version
  1. 365
Platform
  1. Windows
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:

RAJESH NATH

Board Regular
Joined
May 19, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
your look up value should be A2 of sheet3 i.e =vlookup(A2,.....)
 

eaveni4287

New Member
Joined
Aug 20, 2020
Messages
4
Platform
  1. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,889
Messages
5,574,846
Members
412,620
Latest member
sharma7s
Top