Sometimes searching can be tough, when what you’re looking for is trapped inside the language fundamental to the thing on you are searching about. It’s true, sometimes you can’t see the forest for the trees.
A mental block I have with Excel is trying to remember what number a column translates to when your spreadsheet gets really, really wide. Like in the A->CG range. I know that A is 1, and AA is 27…after that things get sketchy.
Who cares? You need this info when you try and use an offset in a vlookup calculation:
=vlookup(some_cell,up_left$corner$:bot_right$corner$,OFFSET,false)
My initial fallback – the Google – would be to search something like:
excel column formula
which of course gives totally useless results – because the search terms themselves are fundamental to Excel: they are the atomic building blocks of Excel. I’d may as well have searched “excel, row, column”.
I’ve run into similar problems trying to search historical info on IP address allocation. Since every node connected to the internet has an IP address, so there’s alot of meaningless hits. I want info on the concept, not the data.
The whole point of this post was for me to document the solution to my column OFFSET problem, so that when I forget it next, I can Google it, and at least hit this page.
Solution 1: The Excel Column Number Formula:
=column()
Solution 2 The Table:
It’s like a formula, but for lazy people:
| Column Heading | |||||
| — | A- | B- | C- | D- | |
| A | 1 | 27 | 53 | 79 | 105 |
| B | 2 | 28 | 54 | 80 | 106 |
| C | 3 | 29 | 55 | 81 | 107 |
| D | 4 | 30 | 56 | 82 | 108 |
| E | 5 | 31 | 57 | 83 | 109 |
| F | 6 | 32 | 58 | 84 | 110 |
| G | 7 | 33 | 59 | 85 | 111 |
| H | 8 | 34 | 60 | 86 | 112 |
| I | 9 | 35 | 61 | 87 | 113 |
| J | 10 | 36 | 62 | 88 | 114 |
| K | 11 | 37 | 63 | 89 | 115 |
| L | 12 | 38 | 64 | 90 | 116 |
| M | 13 | 39 | 65 | 91 | 117 |
| N | 14 | 40 | 66 | 92 | 118 |
| O | 15 | 41 | 67 | 93 | 119 |
| P | 16 | 42 | 68 | 94 | 120 |
| Q | 17 | 43 | 69 | 95 | 121 |
| R | 18 | 44 | 70 | 96 | 122 |
| S | 19 | 45 | 71 | 97 | 123 |
| T | 20 | 46 | 72 | 98 | 124 |
| U | 21 | 47 | 73 | 99 | 125 |
| V | 22 | 48 | 74 | 100 | 126 |
| W | 23 | 49 | 75 | 101 | 127 |
| X | 24 | 50 | 76 | 102 | 128 |
| Y | 25 | 51 | 77 | 103 | 129 |
| Z | 26 | 52 | 78 | 104 | 130 |
Very handy. Actually, I’d never heard of VLOOKUP. I have, however written code in the R language to label columns of a data frame using the Excel column labeling scheme. I typically export from Excel into CSV (comma-separated value) format, and then read it into R. But it’s handy to have Excel-style column names when I need to compare with the original spreadsheet.
Cialis UK…
Buygeneric meds…
……
Buygeneric drugs…
.…
Buynow…
< a href="http://my.gardenguides.com/members/buy-Cialis? buy@Cialis.now…
Buydrugs without prescription…
……
Buygeneric pills…
is overdosing on seroquel painful…
Buydrugs without prescription…
risperdal and carbatrol interactions…
Buygeneric pills…
prozac breast feeding…
Buydrugs without prescription…
……
Buynow it…
.…
Buygeneric drugs…
……
Buygeneric drugs…
……
Buygeneric drugs…
.…
Buygeneric drugs…
.…
Buywithout prescription…
.…
Buynow…
.…
Buygeneric pills…
..…
Buywithout prescription…
.…
Buygeneric pills…
.…
Buygeneric drugs…
.…
Buygeneric drugs…
.…
Buyno prescription…
.…
Buywithout prescription…
..…
Buywithout prescription…
……
Need cheap generic LEVITRA?…
….
.…
….
.…
…
Purchase Cheap Generic Drugs Now!…
…
Purchase Quality Generic Drugs Now!…
.…
ñïñ!!…
.…
thank you!!…
.…
tnx for info!!…
.…
good info!…
.…
ñýíêñ çà èíôó….
.…
tnx for info!…
.…
thanks for information!!…
.…
ñïàñèáî çà èíôó….
.…
ñïàñèáî….
.…
hello!!…
.…
ñïñ çà èíôó!…
.…
ñýíêñ çà èíôó!!…
.…
ñýíêñ çà èíôó!…
.…
tnx!…
.…
ñïñ!!…
.…
ñýíêñ çà èíôó!!…
.…
hello!!…
.…
good!!…
.…
ñýíêñ çà èíôó!!…
.…
ñïàñèáî!…
.…
áëàãîäàðþ!!…