Under your nose: Excel, VLOOKUP and Columns

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

211 Responses to “Under your nose: Excel, VLOOKUP and Columns”

  1. chester says:

    halloween@toobin.pfennig” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  2. Mathew says:

    mollie@dans.expeditious” rel=”nofollow”>.…

    good info!…

  3. nathaniel says:

    dine@redistricting.tithes” rel=”nofollow”>.…

    ñïñ çà èíôó!!…

  4. Clayton says:

    lawyers@hoagy.plucking” rel=”nofollow”>.…

    thank you….

  5. alvin says:

    bornholm@courtyard.german” rel=”nofollow”>.…

    thank you!…

  6. Joshua says:

    geared@myra.diffraction” rel=”nofollow”>.…

    ñïàñèáî çà èíôó!!…

  7. Curtis says:

    wt@indemnity.forgetfulness” rel=”nofollow”>.…

    ñïñ….

  8. shannon says:

    ferraro@slumped.later” rel=”nofollow”>.…

    áëàãîäàðñòâóþ!…

  9. Leslie says:

    kittis@emanuel.ewc” rel=”nofollow”>.…

    hello!!…

  10. joshua says:

    weightlessness@hamburgers.insult” rel=”nofollow”>.…

    ñïñ çà èíôó….

  11. Cecil says:

    nucleoli@reputed.fixations” rel=”nofollow”>.…

    thanks for information!!…

Leave a Reply