Switching from Google Sheets to Excel - Referencing columns

Luke777

New Member
Joined
Aug 10, 2020
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

The company I work for has recently taken the decision to move away from Google Drive and its related products and switch over to using MS Office as our primary office solution. Big mistake in our opinion given that we mostly use basic features and do a LOT of collaborative work (where GD excels - excuse the pun - according to most). Work politics aside, I've got a fair few documents I need to shift over to Excel.

I'm struggling to find a work around for something that I assume has a very simple solution, and that is referencing an entire column from a specific start point to an indeterminate end point. For example, in Sheets I use =MATCH(x, A7:A,0) where x is whatever search_key you fancy, A7:A looks at the entirety of column A starting from A7 for your range, and 0 is the search_type.

My problem is the A7:A part, Excel 2007 refuses to play ball. Any Advice?

Thanks all!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

You'd use MATCH(x,A:A,0), then adjust any formulas using that result to also use entire columns.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,420
Office Version
  1. 365
Platform
  1. Windows
You need to specify an end range like
=MATCH(x, A7:A1000,0)
Just make sure that the 1000 is larger enough to cover all your data, but don't go too large.
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
You need to specify an end range like
=MATCH(x, A7:A1000,0)
Just make sure that the 1000 is larger enough to cover all your data, but don't go too large.

I assume I can get around this by referencing a cell that has a formula that determines the last row in a given column. I must say, seems extremely clunky compared to the Sheets alternative, unfortunately.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You could use dynamic named ranges, or tables, but why can't you just use the entire column? Will you have erroneous matches in the first 6 rows?
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
25
Office Version
  1. 2007
Platform
  1. Windows
You could use dynamic named ranges, or tables, but why can't you just use the entire column? Will you have erroneous matches in the first 6 rows?

Exactly this - might not necessarily be the first 6 but y number of rows will contain irrelevant information. The chance of there actually being an erroneous match is pretty slim, but I'd like to avoid that chance.

I'm not overly familiar with using Dynamic named ranges or tables in my particular use case - I'll look into them and see if they can work with what I'm trying to do (the MATCH formula is actually contained inside an OFFSET). So, if you see any new topics concerning named ranges in the next week or two, say hello lol :)

Thanks for your help though :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Just FYI, OFFSET is a volatile function, and best avoided if possible (or at least minimize the number of times it is used).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,359
Messages
5,624,234
Members
416,017
Latest member
moritz210

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