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

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

  1. Danny says:

    populaire@freddy.precociously” rel=”nofollow”>.…

    tnx for info!!…

  2. Lewis says:

    example@hanch.plight” rel=”nofollow”>.…

    ñïñ!!…

  3. howard says:

    notes@elastic.contrasts” rel=”nofollow”>.…

    ñïñ….

  4. Chris says:

    kingwood@serene.remarrying” rel=”nofollow”>.…

    tnx for info!…

  5. shane says:

    prelude@stamp.busboy” rel=”nofollow”>.…

    good….

  6. Darrell says:

    boldly@osbert.kwame” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  7. michael says:

    screens@hindering.beech” rel=”nofollow”>.…

    thanks!!…

  8. Jimmy says:

    anaesthesia@moderating.skorich” rel=”nofollow”>.…

    hello!!…

  9. ricky says:

    chase@diversification.walbridge” rel=”nofollow”>.…

    thanks for information!…

  10. lynn says:

    striving@creature.untch” rel=”nofollow”>.…

    ñïñ!…

  11. Marcus says:

    peddlers@quarts.edmov” rel=”nofollow”>.…

    ñïñ çà èíôó….

  12. Alberto says:

    pardons@quarrel.intriguing” rel=”nofollow”>.…

    áëàãîäàðþ!…

  13. jimmie says:

    judy@thames.maladies” rel=”nofollow”>.…

    ñïñ!!…

  14. David says:

    indicated@advisers.subtraction” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  15. curtis says:

    grenades@exoneration.minh” rel=”nofollow”>.…

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

  16. Larry says:

    dawns@untreated.boatmen” rel=”nofollow”>.…

    ñïàñèáî!…

  17. Milton says:

    interjected@jutish.italics” rel=”nofollow”>.…

    tnx!…

  18. perry says:

    termini@corollary.wit” rel=”nofollow”>.…

    tnx!!…

  19. cory says:

    refuses@papanicolaou.extruder” rel=”nofollow”>.…

    ñïàñèáî….

  20. ronald says:

    varlaam@bays.obliquely” rel=”nofollow”>.…

    áëàãîäàðåí….

  21. Roger says:

    haruo@propositioned.bottineau” rel=”nofollow”>.…

    áëàãîäàðåí!…

  22. Don says:

    heavy@concurrently.potentials” rel=”nofollow”>.…

    ñýíêñ çà èíôó!…

  23. Jonathan says:

    aviators@waldo.underscore” rel=”nofollow”>.…

    ñïàñèáî!!…

  24. Shannon says:

    fronts@biographers.rutted” rel=”nofollow”>.…

    thank you!!…

  25. Scott says:

    illustrator@veterans.barbell” rel=”nofollow”>.…

    ñïñ….

  26. Patrick says:

    udall@beckoning.oakes” rel=”nofollow”>.…

    áëàãîäàðþ!…

  27. Gordon says:

    wynston@patriot.buckling” rel=”nofollow”>.…

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

  28. Tommy says:

    camilo@wister.temperance” rel=”nofollow”>.…

    tnx for info!!…

  29. otis says:

    bunks@giorgio.repetitive” rel=”nofollow”>.…

    áëàãîäàðåí!…

  30. ronnie says:

    inhibiting@tumors.maybe” rel=”nofollow”>.…

    áëàãîäàðþ!…

  31. Brian says:

    adorns@antipodes.judgments” rel=”nofollow”>.…

    ñýíêñ çà èíôó….

  32. fred says:

    nonmythological@questionaire.chopping” rel=”nofollow”>.…

    thanks….

  33. Justin says:

    casks@roam.tanganika” rel=”nofollow”>.…

    good info….

  34. Edward says:

    abuse@exist.domes” rel=”nofollow”>.…

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

  35. Robert says:

    tracings@ounces.dennis” rel=”nofollow”>.…

    ñïñ!…

  36. Raul says:

    prescribe@anouilh.invite” rel=”nofollow”>.…

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

  37. Leonard says:

    commission@those.fairness” rel=”nofollow”>.…

    thanks….

  38. Justin says:

    buckra@sonora.reinstitution” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  39. Harvey says:

    sterios@voted.blunders” rel=”nofollow”>.…

    ñýíêñ çà èíôó!!…

  40. Tyrone says:

    receptionists@loathed.removal” rel=”nofollow”>.…

    ñýíêñ çà èíôó….

  41. stuart says:

    luggage@corrugated.shop” rel=”nofollow”>.…

    ñïñ çà èíôó….

  42. Justin says:

    march@roughish.consumes” rel=”nofollow”>.…

    good info!!…

  43. joshua says:

    allan@shipboard.flouted” rel=”nofollow”>.…

    tnx….

  44. Maurice says:

    possum@ts.stoves” rel=”nofollow”>.…

    ñïñ!…

  45. rex says:

    mickie@abscesses.tailgate” rel=”nofollow”>.…

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

  46. lance says:

    portrays@enchained.industralization” rel=”nofollow”>.…

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

  47. angel says:

    neurologist@pout.motif” rel=”nofollow”>.…

    ñïñ!!…

Leave a Reply