Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  Education         Excel     Equipment     VBA     PHP     Odds and Sods     Bits and Bobs     News     Plausible Lies     Family     Reviews     Bookmarks     Windows     Word     Hardware     Flatpress     Acrobat     3D Stuff     Overcoming Disabilities     Utilities     Tutorials     Software     Browsers  

Find me on Facebook

facebook-icon-28x28.pngVisit my Briticiser Facebook Page to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.

Using the current row or column number in a lookup formula

Permalink: http://paul.us2uk.eu/?x=entry:entry161017-131301

I was working on a monster spreadsheet which contained over 2000 rows of data spread across 54 columns when I discovered that the spreadsheet I’d been given had about 1000 items (i.e. 1000 rows) missing. I eventually received a revised spreadsheet with the missing items so I was now faced with the task of transferring all of the data which I’d meticulously entered in the original sheet, into the new one.

While it was quite simple to select all of the rows and columns in the original sheet and give it a name (using “Define Name” on the Formulas Toolbar), then use this in a VLOOKUP formula to fill in the cells on the new sheet which had corresponding cells with data on the old sheet, because I had 54 columns of data this would have meant manually changing the column number each time I used the formula in the next column along.

Here’s how to avoid having to do that.

Simply replace the column number with COLUMN() in your VLOOKUP formula because if you use COLUMN without any arguments (i.e. don’t put anything in the parentheses) then the CURRENT column number is returned, which allows you to use eactly the same formula for all of the columns - just copy and paste the formula at the head of each column then copy it down. If you use the dollar symbol in front of the row containing your lookup values, as I did, then this will anchor the lookup to that column so you can simply paste the same formula in all cells and rows in your spreadsheet.

In my spreadsheet the formula at the top of every column was simply this:

=VLOOKUP($A2,‘PSG2AA-TDT-Report-20160718-Paul.xls’!OLDTDT,COLUMN(),FALSE)

The highlighted section is where you enter the name of the range from where you want to pull in your existing data - note that, because this is in a different spreadsheet (it’s important that this is located in the same Windows folder), I entered the name of the file in inverted commas - followed by an exclamation mark (!) - followed by the NAMED RANGE which I’d defined in the Formulas Toolbar.

You can use exactly the same technique if you use a HLOOKUP formula (where you want to use the same formula but want the this to include the current row number (instead of a column) - just replace COLUMN() with ROW() in your formula.

 

Job done… in under 5 minutes!

Incidentally, in all my years of using Excel I can’t ever remember using an HLOOKUP formula!

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.

Support our caregiving

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.